{"text_format":"1","workbook":"xlsx","n_sheets":41,"sheets":["GUIDE_SOLUTIONS","Exercise 2.1","Exercise 2.2","Exercise 2.3","Problem 2.4","Problem 2.5","Problem 2.6","Exercise 3.1","Exercise 3.2","Exercise 3.3","Problem 3.4","Problem 3.5","Problem 3.6","Exercise 4.1","Exercise 4.2","Exercise 4.3","Problem 4.4","Problem 4.5","Problem 4.6","Exercise 5.1","Exercise 5.2","Exercise 5.3","Problem 5.4","Problem 5.5","Problem 5.6","Exercise 10.1","Exercise 10.2","Exercise 10.3","Problem 10.4","Problem 10.5","Problem 10.6","Exercise 12.1","Exercise 12.2","Exercise 12.3","Problem 12.4","Problem 12.5","Problem 12.6","Tutor1","Tutor2","Tutor3","Tutor4"],"not_rendered":{"charts":6,"images":0,"macros":1,"external_links":2,"defined_names":110,"comments":0,"pivot_tables":0},"unopened":[],"bounds":{"document_bytes":16777216,"sheet_cells":100000,"rows_first":2000,"rows_last":500,"rows_between":2000,"halvings":0,"cell_chars":16384,"cell_head_chars":4096},"cells_cut":0,"cells_raw":0,"read":"whole","date_system":"1900"}

{"sheet":"GUIDE_SOLUTIONS","index":0,"n_rows":48,"n_cols":3,"rows_kept":48,"cut":false,"hidden":false,"merged":[]}
[null,"                                                            GUIDE TO REAL OPTION VALUE SOLUTIONS"]
["#1 Session","CHAPTER 2","INTRODUCTION TO BASICS"]
[null,"EXERCISE 2.1","Put Binomial, No Income"]
[null,"EXERCISE 2.2","Call Binomial, No Income"]
[null,"EXERCISE 2.3",{"$f":"C4","v":"Call Binomial, No Income"}]
[null,"PROBLEM 2.4","Black-Scholes Deferral Option"]
[null,"PROBLEM 2.5",{"$f":"C6","v":"Black-Scholes Deferral Option"}]
[null,"PROBLEM 2.6","Margrabe Exchange"]
["#2 Session","CHAPTER 3","STRATEGIES"]
[null,"EXERCISE 3.1","Write Call Strategy: \"Texas Marketing Hedge\""]
[null,"EXERCISE 3.2","Buy Protective Put Strategy"]
[null,"EXERCISE 3.3","Write Extreme Call Strategy: \"Wildcat Texas Hedge\""]
[null,"PROBLEM 3.4","Buy Protective Call Option for Asset User"]
[null,"PROBLEM 3.5","Buy Put funded by Write Call for Leveraged Assets"]
[null,"PROBLEM 3.6","Maria's Unusual Salary Insurance Arrangement"]
["#2 Session","CHAPTER 4","AMERICAN PERPETUAL"]
[null,"EXERCISE 4.1","Real American Perpetual Call Option"]
[null,"EXERCISE 4.2",{"$f":"C17","v":"Real American Perpetual Call Option"}]
[null,"EXERCISE 4.3",{"$f":"C18","v":"Real American Perpetual Call Option"}]
[null,"PROBLEM 4.4","Perpetual American Option PDE"]
[null,"PROBLEM 4.5","Hedging Writing a Real Option"]
[null,"PROBLEM 4.6","Hedging a Long Position in a Real Option"]
["#3 Session","CHAPTER 5","AMERICAN PERPETUAL TWO FACTOR"]
[null,"EXERCISE 5.1","Perpetual American Exchange Real Option"]
[null,"EXERCISE 5.2","Volatile Perpetual American Exchange Real Option"]
[null,"EXERCISE 5.3","Perpetual American QUANTO Exchange Real Option"]
[null,"PROBLEM 5.4",{"$f":"C24","v":"Perpetual American Exchange Real Option"}]
[null,"PROBLEM 5.5",{"$f":"C27","v":"Perpetual American Exchange Real Option"}]
[null,"PROBLEM 5.6","Perpetual American Exchange Option Subsidy"]
["#4 Session","CHAPTER 10","REAL DEBT STRATEGY"]
[null,"EXERCISE 10.1","Equity, Debt Values with Endogenous Bankruptcy"]
[null,"EXERCISE 10.2",{"$f":"C31","v":"Equity, Debt Values with Endogenous Bankruptcy"}]
[null,"EXERCISE 10.3",{"$f":"C31","v":"Equity, Debt Values with Endogenous Bankruptcy"}]
[null,"PROBLEM 10.4","Mortgage Value with Taxes"]
[null,"PROBLEM 10.5","Lender Value"]
[null,"PROBLEM 10.6","Fair Arrangements"]
["#3 Session","CHAPTER 12","SWITCHING OPTIONS"]
[null,"EXERCISE 12.1","Single Output Switch"]
[null,"EXERCISE 12.2","Single In/Out Switch"]
[null,"EXERCISE 12.3","Single Input Switch"]
[null,"PROBLEM 12.4","Single Output Switch"]
[null,"PROBLEM 12.5","Single Output Switch"]
[null,"PROBLEM 12.6","Multiple Input Switching"]
["Tutorial","Tutor 1","Exercise 3.2  "]
[null,"Tutor 2","ISEG GAS Investment Option"]
[null,"Tutor 3","Problem 12.5"]
[null,"Tutor 4","Debt Option"]
["REAL OPTION VALUE EXCEL 4 18 LIS SOLUTIONS"]

{"sheet":"Exercise 2.1","index":1,"n_rows":27,"n_cols":5,"rows_kept":27,"cut":false,"hidden":false,"merged":[]}
["PUT BINOMIAL MODEL"]
["EXERCISE 2.1"]
["s",0.3]
["INTEREST RATE",0.05]
["TIME",1]
["EXERCISE=K",42]
["u",{"$f":"EXP(B3*SQRT(B5))","v":1.3498588075760032},null,"B7=","EXP(B3*SQRT(B5))"]
["d",{"$f":"1/B7","v":0.74081822068171788},null,"B8=","1/B7"]
["a",{"$f":"EXP(B4*B5)","v":1.0512710963760241},null,"B9=","EXP(B4*B5)"]
["p",{"$f":"(B9-B8)/(B7-B8)","v":0.50974086518177042},null,"B10=","(B9-B8)/(B7-B8)"]
["SQUARE FEET",100000,null,null," "]
[" "," ",null,null," "]
["PERIOD 0","PERIOD 1",null,null," "]
[]
[" "]
["RENT/SQ FT"]
[null,{"$f":"$B$7*$A$18","v":56.694069918192135},null,"B17=","$B$7*$A$18"]
[42]
[null,{"$f":"$B$8*$A$18","v":31.114365268632152},null,"B19=","$B$8*$A$18"]
[]
["PUT OPTION VALUE/SQ FT"]
[null,{"$f":"IF((B17<$B$6),(B17-$B$6),0)","v":0},null,"B22=","IF((B17<$B$6),(B17-$B$6),0)"]
[{"$f":"EXP(-B4*B5)*(B22*$B$10+(1-$B$10)*B24)","v":5.076503942460513},null,null,"A23=","EXP(-B4*B5)*(B22*$B$10+(1-$B$10)*B24)"]
[null,{"$f":"IF((B19<$B$6),-(B19-$B$6),0)","v":10.885634731367848},null,"B24=","IF((B19<$B$6),-(B19-$B$6),0)"]
[]
["TOTAL PUT OPTION VALUE"]
[{"$f":"B11*A23","v":507650.39424605132},null,null,"A27=","B11*A23"]

{"sheet":"Exercise 2.2","index":2,"n_rows":34,"n_cols":4,"rows_kept":34,"cut":false,"hidden":false,"merged":[]}
["One-Step Binomial Call "]
["Exercise 2.2"]
["VOLATILITY",0.3]
["RATE",0.05]
["TIME",1]
["up",{"$f":"EXP(B3*SQRT(B5))","v":1.3498588075760032}]
["down",{"$f":"1/B6","v":0.74081822068171788}]
["probability",{"$f":"(EXP(B4*B5)-B7)/(B6-B7)","v":0.50974086518177042}]
[]
[{"$f":"A6","v":"up"},"EXP(B3*SQRT(B5))"]
[{"$f":"A7","v":"down"},"1/B6"]
[{"$f":"A8","v":"probability"},"(EXP(B4*B5)-B7)/(B6-B7)"]
[]
[]
["Time",0,1]
[null," "]
["Expected",null,{"$f":"B18*B6","v":94490.116530320229},"u"]
["Earnings",70000]
[null,null,{"$f":"B18*B7","v":51857.275447720254},"d"]
[]
[null,null,{"$f":"B22","v":75000},"u"]
["Cost",75000]
[null,null,{"$f":"B22","v":75000},"d"]
[]
[null,null,{"$f":"MAX((C17-C21),0)","v":19490.116530320229},"u"]
["Call Value",{"$f":"EXP(-B4*B5)*(C25*B8+(1-B8)*C27)","v":9450.3776398941245}]
[null,null,{"$f":"MAX((C19-C23),0)","v":0},"d"]
["C25=",null,"MAX((C17-C21),0)"]
["C27=",null,"MAX((C19-C23),0)"]
["B26=","                                                EXP(-B4*B5)*(C25*B8+(1-B8)*C27)"]
[]
[]
[" ",null," "]
[" "]

{"sheet":"Exercise 2.3","index":3,"n_rows":32,"n_cols":4,"rows_kept":32,"cut":false,"hidden":false,"merged":[]}
["One-Step Binomial Call "]
["Exercise 2.3"]
["VOLATILITY",0.4]
["RATE",0.1]
["TIME",1]
["up",{"$f":"EXP(B3*SQRT(B5))","v":1.4918246976412703}]
["down",{"$f":"1/B6","v":0.67032004603563933}]
["probability",{"$f":"(EXP(B4*B5)-B7)/(B6-B7)","v":0.52933464368107019}]
[]
[{"$f":"A6","v":"up"},"EXP(B3*SQRT(B5))"]
[{"$f":"A7","v":"down"},"1/B6"]
[{"$f":"A8","v":"probability"},"(EXP(B4*B5)-B7)/(B6-B7)"]
[]
[]
["Time",0,1]
[null," "]
[" ",null,{"$f":"B18*B6","v":62.656637300933355},"u"]
["Rent",42]
[null,null,{"$f":"B18*B7","v":28.15344193349685},"d"]
[]
[null,null,{"$f":"B22","v":45},"u"]
["Exercise Price",45]
[null,null,{"$f":"B22","v":45},"d"]
[]
[null,null,{"$f":"MAX((C17-C21),0)","v":17.656637300933355},"u"]
["Call Value",{"$f":"EXP(-B4*B5)*(C25*B8+(1-B8)*C27)","v":8.4568546470345218}]
[null,null,{"$f":"MAX((C19-C23),0)","v":0},"d"]
["C25=",null,"MAX((C17-C21),0)"]
["C27=",null,"MAX((C19-C23),0)"]
["B26=","                                                EXP(-B4*B5)*(C25*B8+(1-B8)*C27)"]
["SF",100000]
["Total Call Option Value ",{"$f":"B26*B31","v":845685.46470345219}]

{"sheet":"Problem 2.4","index":4,"n_rows":20,"n_cols":3,"rows_kept":20,"cut":false,"hidden":false,"merged":[]}
["DEFERRAL OPTION"]
["Problem 2.4"]
[]
[" "," "]
["ASSET VALUE",500]
["INVESTMENT COST",450]
["RISKLESS RATE",0.04]
["DIVIDEND",0.04]
["TIME / YEARS",5]
["VOLATILITY",0.2]
["CALL PRICE",{"$f":"B5*B15*EXP(-B8*B9)-B6*B16*EXP(-B7*B9)","v":91.145664723121712},"B5*B15*EXP(-B8*B9)-B6*B16*EXP(-B7*B9)"]
[]
["d1",{"$f":"(LN(B5/B6)+(B7-B8+B10^2/2)*B9)/(B10*SQRT(B9))","v":0.45920007290530968},"(LN(B5/B6)+(B7-B8+B10^2/2)*B9)/(B10*SQRT(B9))"]
["d2",{"$f":"B13-B10*SQRT(B9)","v":1.1986477405351692E-2},"B13-B10*SQRT(B9)"]
["N(d1)",{"$f":"NORMSDIST(B13)","v":0.676954750886957},"NORMSDIST(B13)"]
["N(d2)",{"$f":"NORMSDIST(B14)","v":0.50478179812514568},"NORMSDIST(B14)"]
[]
["SF",100000]
["TOTAL REAL CALL OPTION VALUE",{"$f":"B11*B18","v":9114566.472312171}]
["INTRINSIC REAL CALL VALUE",{"$f":"(B5-B6)*B18","v":5000000}]

{"sheet":"Problem 2.5","index":5,"n_rows":19,"n_cols":3,"rows_kept":19,"cut":false,"hidden":false,"merged":[]}
["DEFERRAL OPTION"]
[" "]
["Problem 2.5"]
[" "," "]
["ASSET VALUE",1200]
["INVESTMENT COST",1338]
["RISKLESS RATE",0.05]
["DIVIDEND",0]
["TIME / YEARS",2]
["VOLATILITY",0.5]
["CALL PRICE",{"$f":"B5*B15*EXP(-B8*B9)-B6*B16*EXP(-B7*B9)","v":327.75501946852518},"B5*B15*EXP(-B8*B9)-B6*B16*EXP(-B7*B9)"]
[]
["d1",{"$f":"(LN(B5/B6)+(B7-B8+B10^2/2)*B9)/(B10*SQRT(B9))","v":0.34103137107986436},"(LN(B5/B6)+(B7-B8+B10^2/2)*B9)/(B10*SQRT(B9))"]
["d2",{"$f":"B13-B10*SQRT(B9)","v":-0.36607541010668321},"B13-B10*SQRT(B9)"]
["N(d1)",{"$f":"NORMSDIST(B13)","v":0.63346001742384173},"NORMSDIST(B13)"]
["N(d2)",{"$f":"NORMSDIST(B14)","v":0.35715440288093858},"NORMSDIST(B14)"]
[]
["Assumption"]
["This investment opportunity is a European, not an American, call option."]

{"sheet":"Problem 2.6","index":6,"n_rows":28,"n_cols":3,"rows_kept":28,"cut":false,"hidden":false,"merged":[]}
["MARGRABE EXCHANGE"]
["Problem 2.6"," "]
["RESIDENTIAL TENANTS",1600000]
["RETAIL TENANTS",1750000]
["TENANT SWITCHING TIME",1]
[" "," "]
["BENEFIT YIELD",0.04]
["COST YIELD",0.04]
["BENEFIT VOLATILITY",0.2]
["COST VOLATILITY",0.2]
["EXCHANGE VOLATILITY",{"$f":"SQRT(B9^2 + B10^2 -2*B13*B10*B9)","v":0.2},"SQRT(B9^2 + B10^2 -2*B13*B10*B9)"]
[]
["CORRELATION ",0.5," "]
["EXCHANGE CALL OPTION VALUE",{"$f":"B3*B18*EXP(-B7*B5)-B4*B19*EXP(-B8*B5)","v":68736.540599974745},"B3*B18*EXP(-B7*B5)-B4*B19*EXP(-B8*B5)"]
[]
["d1",{"$f":"(LN(B3/B4)+(B8-B7+(B11^2)/2)*B5)/(B11*SQRT(B5))","v":-0.34806079344843577},"(LN(B3/B4)+(B8-B7+(B11^2)/2)*B5)/(B11*SQRT(B5))"]
["d2",{"$f":"B16-B11*SQRT(B5)","v":-0.54806079344843583},"B16-B11*SQRT(B5)"]
["N(d1)",{"$f":"NORMSDIST(B16)","v":0.36389726390554022},"NORMSDIST(B16)"]
["N(d2)",{"$f":"NORMSDIST(B17)","v":0.29182508012097036},"NORMSDIST(B17)"]
[]
["Margrabe Exchange Real Option is worth",null,{"$f":"B14","v":68736.540599974745}]
["ASSUMES: volatilities and correlation are constant, this a European option,"]
["Several assumptions required."," "]
[" "," "]
["Investment in facility for retail tenants worth ",null,{"$f":"B4-C28","v":50000}]
["Option to switch tenants in one year worth",null,{"$f":"B14","v":68736.540599974745}]
["So value of modular facility worth",null,{"$f":"C25+C26","v":118736.54059997475}]
["Investment cost ",null,1700000]

{"sheet":"Exercise 3.1","index":7,"n_rows":33,"n_cols":14,"rows_kept":33,"cut":false,"hidden":false,"merged":[]}
["REAL OPTION PAYOFFS"]
["Exercise 3.1"]
["OWN ASSET-DEBT",{"$f":"B12-$B$6","v":-150},{"$f":"C12-$B$6","v":-125},-100,-75,-50,-25,0,25,50,75,100,125,150]
["WRITE REAL CALL INTRINSIC",{"$f":"IF(B3>0,-B3+$B$7,$B$7)","v":50},{"$f":"IF(C3>0,-C3+$B$7,$B$7)","v":50},50,50,50,50,50,25,0,-25,-50,-75,-100]
["STRATEGY RESULT",{"$f":"B3+B4","v":-100},{"$f":"C3+C4","v":-75},-50,-25,0,25,50,50,50,50,50,50,50]
["Debt, K",150," "," ",{"$f":"D6","v":" "}," "," "," "," "," "," "," "," "," "]
["Option cost, y",{"$f":"$B$33","v":50},"Assumed"]
[" Own asset subject to debt K, give investor an option to buy net asset at nil cost, for immediate payment of option premium of y."]
["  STRATEGY RESULT equals write real put on net asset value."]
["WRITE REAL CALL INTRINSIC","IF(B3>0,-B3+$B$7,$B$7)"]
["STRATEGY RESULT","B3+B4","MIN(B12-$B$6+$B$7,$B$7)"]
["ASSET VALUE",0,{"$f":"B12+25","v":25},{"$f":"C12+25","v":50},75,100,125,150,175,200,225,250,275,300]
[{"$f":"A5","v":"STRATEGY RESULT"},{"$f":"MIN(B12-$B$6+$B$7,$B$7)","v":-100},{"$f":"MIN(C12-$B$6+$B$7,$B$7)","v":-75},-50,-25,0,25,50,50,50,50,50,50,50]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
["Option cost, y",50]

{"sheet":"Exercise 3.2","index":8,"n_rows":33,"n_cols":14,"rows_kept":33,"cut":false,"hidden":false,"merged":[]}
["REAL OPTION PAYOFFS"]
["Exercise 3.2",0,{"$f":"B2+25","v":25},{"$f":"C2+25","v":50},75,100,125,150,175,200,225,250,275,300]
["OWN ASSET-DEBT",{"$f":"0-$B$6","v":-150},{"$f":"B3+25","v":-125},-100,-75,-50,-25,0,25,50,75,100,125,150]
["BUY REAL PUT INTRINSIC",{"$f":"IF(B3<0,-B3-$B$7,-$B$7)","v":75},{"$f":"IF(C3<0,-C3-$B$7,-$B$7)","v":50},25,0,-25,-50,-75,-75,-75,-75,-75,-75,-75]
["STRATEGY RESULT",{"$f":"B4+B3","v":-75},{"$f":"C4+C3","v":-75},-75,-75,-75,-75,-75,-50,-25,0,25,50,75]
["Debt, K",150," "," ",{"$f":"D6","v":" "}," "," "," "," "," "," "," "," "," "]
["Option cost, y",{"$f":"B33","v":75}]
["   Own asset subject to debt K, buy right to sell asset at debt amount K, for immediate payment of option premium of y."]
["  STRATEGY RESULT equals buy real call on net asset value at nil exercise cost."]
[{"$f":"A4","v":"BUY REAL PUT INTRINSIC"},"IF(B3<0,-B3-$B$7,-$B$7)"]
[{"$f":"A5","v":"STRATEGY RESULT"},"B3+B4"]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
["Option cost, y",75]

{"sheet":"Exercise 3.3","index":9,"n_rows":57,"n_cols":16,"rows_kept":57,"cut":false,"hidden":false,"merged":[]}
["SALFORD TRIATHLON ARRANGEMENT"]
["Exercise 3.3"]
["POUNDS"]
["REVENUE",100]
["FIXED COSTS",50000]
["VARIABLE COSTS",50]
["TRIATHLETES",0,{"$f":"B7+200","v":200},{"$f":"C7+200","v":400},600,800,1000,1200,1400,1600,1800,2000,2200,2400,2600]
[" "]
["NO ARRANGEMENT"]
["REVENUE",{"$f":"$B$4*B7","v":0},20000,40000,60000,80000,100000,120000,140000,160000,180000,200000,220000,240000,260000,"$B$4*P7"]
["FIXED COSTS",{"$f":"$B$5","v":50000},50000,50000,50000,50000,50000,50000,50000,50000,50000,50000,50000,50000,50000,"$B$5"]
["VARIABLE COSTS",{"$f":"$B$6*B7","v":0},10000,20000,30000,40000,50000,60000,70000,80000,90000,100000,110000,120000,130000,"$B$6*P7"]
["PROFIT",{"$f":"B10-B11-B12","v":-50000},{"$f":"C10-C11-C12","v":-40000},{"$f":"D10-D11-D12","v":-30000},-20000,-10000,0,10000,20000,30000,40000,50000,60000,70000,80000,"P10-P11-P12"]
[]
["WITH SALFORD COUNCIL ARRANGEMENT"]
["REVENUE",{"$f":"B4","v":100}]
["FIXED COSTS",{"$f":"B5-B19","v":10000}]
["VARIABLE COSTS",50]
["FEE",{"$f":"B55","v":40000}]
["SALFORD SHARE",{"$f":"B56","v":0.75}]
["SHARE START",{"$f":"B57","v":100000}]
[]
["REVENUE",{"$f":"$B$16*B26","v":0},20000,40000,60000,80000,100000,120000,140000,160000,180000,200000,220000,240000,260000,"$B$16*P26"]
["FIXED COSTS",{"$f":"$B$17","v":10000},10000,10000,10000,10000,10000,10000,10000,10000,10000,10000,10000,10000,10000,"$B$17"]
["VARIABLE COSTS",{"$f":"$B$6*B26","v":0},10000,20000,30000,40000,50000,60000,70000,80000,90000,100000,110000,120000,130000,"$B$6*P26"]
["TRIATHLETES",{"$f":"B7","v":0},{"$f":"C7","v":200},400,600,800,1000,1200,1400,1600,1800,2000,2200,2400,2600," "]
["SALFORD SHARE",{"$f":"IF(B23>$B$21,$B$20*B23,0)","v":0},0,0,0,0,0,90000,105000,120000,135000,150000,165000,180000,195000,"IF(P23>$B$21,$B$20*P23,0)"]
["PROFIT SHARING",{"$f":"B23-B24-B25-B27","v":-10000},{"$f":"C23-C24-C25-C27","v":0},10000,20000,30000,40000,-40000,-45000,-50000,-55000,-60000,-65000,-70000,-75000,"P23-P24-P25-P27"]
["PROFIT, NO SHARE",{"$f":"B13","v":-50000},-40000,-30000,-20000,-10000,0,10000,20000,30000,40000,50000,60000,70000,80000," "]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
["FEE",40000]
["SALFORD SHARE",0.75]
["SHARE START",100000]

{"sheet":"Problem 3.4","index":10,"n_rows":33,"n_cols":22,"rows_kept":33,"cut":false,"hidden":false,"merged":[]}
["REAL OPTION PAYOFFS"]
["Problem 3.4",0,{"$f":"B2+25","v":25},{"$f":"C2+25","v":50},75,100,125,150,175,200,225,250,275,300,325,350,375,400,425,450,475,500]
["NET ASSET VALUE",{"$f":"B2-$B$7","v":-300},{"$f":"C2-$B$7","v":-275},-250,-225,-200,-175,-150,-125,-100,-75,-50,-25,0,25,50,75,100,125,150,175,200]
["REQUIRE USE OF NET ASSET",{"$f":"-B3","v":300},{"$f":"-C3","v":275},250,225,200,175,150,125,100,75,50,25,0,-25,-50,-75,-100,-125,-150,-175,-200]
["BUY REAL CALL INTRINSIC",{"$f":"IF(B3>0,B3-$B$8,-$B$8)","v":-75},{"$f":"IF(C3>0,C3-$B$8,-$B$8)","v":-75},-75,-75,-75,-75,-75,-75,-75,-75,-75,-75,-75,-50,-25,0,25,50,75,100,125]
["STRATEGY RESULT",{"$f":"B4+B5","v":225},{"$f":"C4+C5","v":200},175,150,125,100,75,50,25,0,-25,-50,-75,-75,-75,-75,-75,-75,-75,-75,-75]
["Debt, K",300," "," ",{"$f":"D7","v":" "}," "," "," "," "," "," "," "," "," "]
["Option cost, y",{"$f":"B33","v":75}]
["Require use of  asset subject to debt K, buy option to buy net asset at nil cost, for immediate payment of option premium of y."]
["  STRATEGY RESULT equals buy real put on net asset value."]
[{"$f":"A5","v":"BUY REAL CALL INTRINSIC"},"IF(B3>0,B3-$B$8,-$B$8)"]
["STRATEGY RESULT","B4+B5"]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
["Option cost, y",75]

{"sheet":"Problem 3.5","index":11,"n_rows":16,"n_cols":12,"rows_kept":16,"cut":false,"hidden":false,"merged":[]}
["Problem 3.5","REAL OPTION PAYOFFS"]
["OWN ASSET-DEBT",{"$f":"B16-$B$8","v":-500},-400,-300,-200,-100,0,100,200,300,400,500]
["WRITE REAL CALL AT K2 INTRINSIC ",{"$f":"IF(B2>$B$6,$B$6-B2+$B$9,$B$9)","v":25},{"$f":"IF(C2>$B$6,$B$6-C2+$B$9,$B$9)","v":25},25,25,25,25,25,-75,-175,-275,-375]
["BUY REAL PUT AT K1 INTRINSIC",{"$f":"IF(B2<$B$7,-B2-$B$7-$B$10,-$B$10)","v":475},{"$f":"IF(C2<$B$7,-C2-$B$7-$B$10,-$B$10)","v":375},275,175,75,-25,-25,-25,-25,-25,-25]
["STRATEGY RESULT",{"$f":"B2+B3+B4","v":0},{"$f":"C2+C3+C4","v":0},0,0,0,0,100,100,100,100,100]
["K2",100]
["K1",0]
["Debt, K",500," "," ",{"$f":"D8","v":" "}," "," "," "," "," "," "," "]
["Option cost, y2",25]
["Option cost, y1",25]
["   Own asset subject to debt K, buy a \"costless protective put\" (=protective collar)."]
["  STRATEGY RESULT equals real collar."]
[{"$f":"A3","v":"WRITE REAL CALL AT K2 INTRINSIC "},"IF(B2>$B$6,$B$6-B2+$B$9,$B$9)"]
[{"$f":"A4","v":"BUY REAL PUT AT K1 INTRINSIC"},"IF(B2<$B$7,-B2-$B$7-$B$10,-$B$10)"]
["STRATEGY RESULT","B2+B3+B4"]
["ASSET VALUE",0,{"$f":"B16+100","v":100},{"$f":"C16+100","v":200},300,400,500,600,700,800,900,1000]

{"sheet":"Problem 3.6","index":12,"n_rows":44,"n_cols":15,"rows_kept":44,"cut":false,"hidden":false,"merged":[]}
["MARIA KNOX:  INSURING HER EARLY CAREER EARNINGS IN A CONTINGENT CLAIM ARRANGEMENT"]
["Problem 3.6"]
["MARIA STARTING SALARY",30000,{"$f":"B3+10000","v":40000},{"$f":"C3+10000","v":50000},60000,70000,80000,90000,100000,110000,120000,130000,140000,150000,160000]
[" "]
["FUD BONUS INSURANCE PREMIUM",{"$f":"B41","v":20000}]
["BONUS",{"$f":"B42","v":30000}]
["LOW PAY BARRIER",{"$f":"B43","v":50000}]
["HIGH PAY BARRIER",{"$f":"B44","v":150000}]
[" "]
["LOW PAY  BONUS",{"$f":"IF(B3<$B$7,$B$6,0)","v":30000},{"$f":"IF(C3<$B$7,$B$6,0)","v":30000},0,0,0,0,0,0,0,0,0,0,0,0]
["HIGH PAY  BONUS",{"$f":"IF(B3>$B$8,$B$6,0)","v":0},{"$f":"IF(C3>$B$8,$B$6,0)","v":0},0,0,0,0,0,0,0,0,0,0,0,30000]
["TOTAL PAY + BONUS-FUD",{"$f":"B3+B10+B11-$B$5","v":40000},50000,30000,40000,50000,60000,70000,80000,90000,100000,110000,120000,130000,170000]
[{"$f":"A3","v":"MARIA STARTING SALARY"},{"$f":"B3","v":30000},40000,50000,60000,70000,80000,90000,100000,110000,120000,130000,140000,150000,160000]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[{"$f":"A5","v":"FUD BONUS INSURANCE PREMIUM"},20000]
[{"$f":"A6","v":"BONUS"},30000]
[{"$f":"A7","v":"LOW PAY BARRIER"},50000]
[{"$f":"A8","v":"HIGH PAY BARRIER"},150000]

{"sheet":"Exercise 4.1","index":13,"n_rows":18,"n_cols":3,"rows_kept":18,"cut":false,"hidden":false,"merged":[]}
["                                                       Perpetual American Option"]
["Exercise 4.1"]
["V",500]
["K",450]
["s",0.2]
["r",0.04]
["d",0.04]
["ROV",{"$f":"IF(B3<B12,(B12-B4)*(B3/B12)^B14,B10)","v":138.88888888888894},"IF(B3<B12,(B12-B4)*(B3/B12)^B14,B10)"]
["ROV",{"$f":"IF(B3<B12,B13*(B3^B14),B10)","v":138.88888888888883},"IF(B3<B12,B13*(B3^B14),B10)"]
["V-K",{"$f":"B3-B4","v":50},"B3-B4"]
["F'(V)",{"$f":"IF(B3<B12,B13*B14*(B3^(B14-1)),1)","v":0.55555555555555536},"IF(B3<B12,B13*B14*(B3^(B14-1)),1)"]
["V*",{"$f":"(B14/(B14-1))*B4","v":900.00000000000023},"(B14/(B14-1))*B4"]
["A",{"$f":"(B12-B4)/(B12^B14)","v":5.5555555555555664E-4},"(B12-B4)/(B12^B14)"]
["b1",{"$f":"0.5-(B6-B7)/(B5^2)+SQRT(((B6-B7)/(B5^2)-0.5)^2 + 2*B6/(B5^2))","v":1.9999999999999998}]
["SF",100000]
[]
["ROV",{"$f":"B9*B15","v":13888888.888888882}]
["V* TOTAL",{"$f":"B12*B15","v":90000000.00000003}]

{"sheet":"Exercise 4.2","index":14,"n_rows":32,"n_cols":3,"rows_kept":32,"cut":false,"hidden":false,"merged":[]}
["                                                       Perpetual American Option"]
["Exercise 4.2"]
["V",750]
["K",850]
["s",0.5]
["r",0.04]
["d",0.04]
["ROV",{"$f":"IF(B3<B12,(B12-B4)*(B3/B12)^B14,B10)","v":385.5514036421655},"IF(B3<B12,(B12-B4)*(B3/B12)^B14,B10)"]
["ROV",{"$f":"IF(B3<B12,B13*(B3^B14),B10)","v":385.55140364216555},"IF(B3<B12,B13*(B3^B14),B10)"]
["V-K",{"$f":"B3-B4","v":-100},"B3-B4"]
["F'(V)",{"$f":"IF(B3<B12,B13*B14*(B3^(B14-1)),1)","v":0.64514750426605649},"IF(B3<B12,B13*B14*(B3^(B14-1)),1)"]
["V*",{"$f":"(B14/(B14-1))*B4","v":4183.549771868792},"(B14/(B14-1))*B4"]
["A",{"$f":"(B12-B4)/(B12^B14)","v":9.5044721811912397E-2},"(B12-B4)/(B12^B14)"]
["b1",{"$f":"0.5-(B6-B7)/(B5^2)+SQRT(((B6-B7)/(B5^2)-0.5)^2 + 2*B6/(B5^2))","v":1.254983443527075},"0.5-(B6-B7)/(B5^2)+SQRT(((B6-B7)/(B5^2)-0.5)^2 + 2*B6/(B5^2))"]
[" "," "]
["ODE",{"$f":"0.5*(B5^2)*(B3^2)*B17+(B6-B7)*B3*B11-B6*B9","v":0}]
["F''(V)",{"$f":"IF(B3<B12,B13*B14*(B14-1)*(B3^(B14-2)),0)","v":2.1933590962754321E-4}]
["F'(V*)",{"$f":"B13*B14*(B12^(B14-1))","v":1}]
["F(V*)",{"$f":"IF(B3<B12,B13*(B12^B14),B10)","v":3333.5497718687925}]
["V*-K",{"$f":"B12-B4","v":3333.549771868792}]
["SF",2000]
["TOTAL ROV",{"$f":"B9*B21","v":771102.80728433107}]
["COST",200000]
[" "," "]
["SECURITY ANALYSIS OF CITY LTD."]
["ASSETS","LIAB"]
[{"$f":"750000-B23","v":550000},500000]
[{"$f":"$B$22","v":771102.80728433107},{"$f":"A29-B27","v":821102.80728433095},"NET WORTH"]
[{"$f":"A27+A28","v":1321102.8072843309}]
[]
["SHARES",1000000]
[null,{"$f":"B28/B31","v":0.82110280728433094},"NET REAL OPTION VALUE/SHARE"]

{"sheet":"Exercise 4.3","index":15,"n_rows":32,"n_cols":3,"rows_kept":32,"cut":false,"hidden":false,"merged":[]}
["                                                       Perpetual American Option"]
["Exercise 4.3"]
["UMIT",1.1000000000000001]
["COST",1.7]
["s",0.2]
["r",0.04]
["d",0.04]
["ROV",{"$f":"IF(B3<B12,(B12-B4)*(B3/B12)^B14,B10)","v":0.17794117647058838},"IF(B3<B12,(B12-B4)*(B3/B12)^B14,B10)"]
["F(V)",{"$f":"IF(B3<B12,B13*(B3^B14),B10)","v":0.17794117647058838},"IF(B3<B12,B13*(B3^B14),B10)"]
["V-K",{"$f":"B3-B4","v":-0.59999999999999987},"B3-B4"]
["F'(V)",{"$f":"IF(B3<B12,B13*B14*(B3^(B14-1)),1)","v":0.32352941176470607},"IF(B3<B12,B13*B14*(B3^(B14-1)),1)"]
["V*",{"$f":"(B14/(B14-1))*B4","v":3.4000000000000008},"(B14/(B14-1))*B4"]
["A",{"$f":"(B12-B4)/(B12^B14)","v":0.14705882352941185},"(B12-B4)/(B12^B14)"]
["b1",{"$f":"0.5-(B6-B7)/(B5^2)+SQRT(((B6-B7)/(B5^2)-0.5)^2 + 2*B6/(B5^2))","v":1.9999999999999998}]
["b1=","0.5-(B6-B7)/(B5^2)+SQRT(((B6-B7)/(B5^2)-0.5)^2 + 2*B6/(B5^2))"]
["ODE",{"$f":"0.5*(B5^2)*(B3^2)*B17+(B6-B7)*B3*B11-B6*B9","v":0}]
["F''(V)",{"$f":"IF(B3<B12,B13*B14*(B14-1)*(B3^(B14-2)),0)","v":0.29411764705882359}]
["F'(V*)",{"$f":"B13*B14*(B12^(B14-1))","v":1.0000000000000004}]
["F(V*)",{"$f":"IF(B3<B12,B13*(B12^B14),B10)","v":1.7000000000000008}]
["V*-K",{"$f":"B12-B4","v":1.7000000000000008}]
[]
["IT IS HARD TO IMAGINE THAT THERE IS ANOTHER"]
["TRADED ASSET CORRELATED WITH UMIT, THAT"]
["THE VOLATILITY OF UMIT CAN BE ESTIMATED"]
["ACCURATELY, OR THAT THIS ACQUISITION COULD"]
["BE PERPETUAL."]
[]
["BUT SOLVING THE PROBLEM INDICATES A THOUGHT"]
["PROCESS, WHEREBY UMAN MIGHT CONSIDER DEFERRAL"]
["UNTIL UMIT IS WORTH OVER 3 TIMES CURRENT VALUE,"]
["OR A GOVERNMENT REDUCES THE EFFECTIVE COST,"]
["UNLESS UMAN HAS OTHER OBJECTIVE FUNCTIONS."]

{"sheet":"Problem 4.4","index":16,"n_rows":25,"n_cols":3,"rows_kept":25,"cut":false,"hidden":false,"merged":[]}
["                                                       Perpetual American Option"]
["Problem 4.4"]
["V",500]
["K",450]
["s",0.2]
["r",0.04]
["d",0.04]
[]
["F(V)",{"$f":"IF(B3<B12,B13*(B3^B14),B10)","v":138.88888888888883},"IF(B3<B12,B13*(B3^B14),B10)"]
["V-K",{"$f":"B3-B4","v":50},"B3-B4"]
["F'(V)",{"$f":"IF(B3<B12,B13*B14*(B3^(B14-1)),1)","v":0.55555555555555536},"IF(B3<B12,B13*B14*(B3^(B14-1)),1)"]
["V*",{"$f":"(B14/(B14-1))*B4","v":900.00000000000023},"(B14/(B14-1))*B4"]
["A",{"$f":"(B12-B4)/(B12^B14)","v":5.5555555555555664E-4},"(B12-B4)/(B12^B14)"]
["b1",{"$f":"0.5-(B6-B7)/(B5^2)+SQRT(((B6-B7)/(B5^2)-0.5)^2 + 2*B6/(B5^2))","v":1.9999999999999998}]
["b1=","0.5-(B6-B7)/(B5^2)+SQRT(((B6-B7)/(B5^2)-0.5)^2 + 2*B6/(B5^2))"]
["ODE",{"$f":"0.5*(B5^2)*(B3^2)*B17+(B6-B7)*B3*B11-B6*B9","v":0},"0.5*(B5^2)*(B3^2)*B17+(B6-B7)*B3*B11-B6*B9"]
["F''(V)",{"$f":"IF(B3<B12,B13*B14*(B14-1)*(B3^(B14-2)),0)","v":1.1111111111111113E-3},"IF(B3<B12,B13*B14*(B14-1)*(B3^(B14-2)),0)"]
["F'(V*)",{"$f":"B13*B14*(B12^(B14-1))","v":1.0000000000000002},"B13*B14*(B12^(B14-1))"]
["F(V*)",{"$f":"IF(B3<B12,B13*(B12^B14),B10)","v":450.00000000000023},"IF(B3<B12,B13*(B12^B14),B10)"]
["V*-K",{"$f":"B12-B4","v":450.00000000000023},"B12-B4"]
[]
["INSERTING DELTA, GAMMA  AND THE F(V) FUNCTION"]
["INTO ODE RESULTS IN THE ODE EQUAL TO ZERO."]
["VALUE MATCHING AND SMOOTH PASTING CONDITIONS"]
["ARE MET."]

{"sheet":"Problem 4.5","index":17,"n_rows":71,"n_cols":6,"rows_kept":71,"cut":false,"hidden":false,"merged":[]}
[null,"                                                       Hedging a Perpetual American Option"]
["Problem 4.5","PER SF","TOTAL"]
["V",750,{"$f":"B3*C15","v":1500000}]
["K",850,{"$f":"B4*C15","v":1700000}]
["s",0.5,{"$f":"B5","v":0.5}]
["r",0.04,{"$f":"B6","v":0.04}]
["d",0.04,{"$f":"B7","v":0.04}]
[]
["F(V)",{"$f":"IF(B3<B12,B13*(B3^B14),B10)","v":385.55140364216555},{"$f":"B9*C15","v":771102.80728433107}]
["V-K",{"$f":"B3-B4","v":-100},{"$f":"C3-C4","v":-200000}]
["F'(V)",{"$f":"IF(B3<B12,B13*B14*(B3^(B14-1)),1)","v":0.64514750426605649},{"$f":"IF(C3<C12,C13*C14*(C3^(C14-1)),1)","v":0.64514750426605616}]
["V*",{"$f":"(B14/(B14-1))*B4","v":4183.549771868792},{"$f":"(C14/(C14-1))*C4","v":8367099.5437375847}]
["A",{"$f":"(B12-B4)/(B12^B14)","v":9.5044721811912397E-2},{"$f":"(C12-C4)/(C12^C14)","v":1.3684218762133868E-2}]
["b1",{"$f":"0.5-(B6-B7)/(B5^2)+SQRT(((B6-B7)/(B5^2)-0.5)^2 + 2*B6/(B5^2))","v":1.254983443527075},{"$f":"0.5-(C6-C7)/(C5^2)+SQRT(((C6-C7)/(C5^2)-0.5)^2 + 2*C6/(C5^2))","v":1.254983443527075}]
["SF",null,2000]
["CITY OTHER NET ASSETS",null,250000,null," "," "]
["COST CITY'S ROV",null,200000]
[]
["CITY LTD VALUE",null,{"$f":"C9+C16-C17","v":821102.80728433107},null," "," "]
["CITY LTD ISSUED FOR "," ",900000,null," "," "]
["DELTA HEDGE POSITION: V",null,{"$f":"C11*C3","v":967721.25639908423},"C11*C3"," "," "]
[" "," "]
[]
["INITIAL PROFITS",null,{"$f":"C20-C19","v":78897.192715668934},"C20-C19"]
["EVENTUAL PROFITS DEPEND ON HEDGING INACCURACY & TRANSACTION COSTS."]
[]
[null,"ROV NUMERICAL DELTA",null,null,"10% DECREASE","10% INCREASE"]
[{"$f":"A3","v":"V"},675,{"$f":"B3","v":750},825,{"$f":"-1+B28/C28","v":-9.9999999999999978E-2},{"$f":"D28/C28-1","v":0.10000000000000009}]
[{"$f":"A4","v":"K"},850,{"$f":"B4","v":850},850]
["s",0.5,{"$f":"B5","v":0.5},0.5]
["r",0.04,{"$f":"B6","v":0.04},0.04]
["d",0.04,{"$f":"B7","v":0.04},0.04]
[" "," "," "," ","AS % V"]
[{"$f":"A9","v":"F(V)"},{"$f":"IF(B28<B37,B38*(B28^B39),B35)","v":337.79825012267383},{"$f":"B9","v":385.55140364216555},{"$f":"IF(D28<D37,D38*(D28^D39),D35)","v":434.53966290232796},{"$f":"(B34-C34)/(B28-C28)","v":0.6367087135932229},{"$f":"(D34-C34)/(D28-C28)","v":0.65317679013549879}]
[{"$f":"A10","v":"V-K"},{"$f":"B28-B29","v":-175},-100,{"$f":"D28-D29","v":-25}]
[{"$f":"A11","v":"F'(V)"},{"$f":"IF(B28<B37,B38*B39*(B28^(B39-1)),1)","v":0.62804623875018295},0.64514750426605649,{"$f":"IF(D28<D37,D38*D39*(D28^(D39-1)),1)","v":0.66101828181607036}]
[{"$f":"A12","v":"V*"},{"$f":"(B39/(B39-1))*B29","v":4183.549771868792},4183.549771868792,{"$f":"(D39/(D39-1))*D29","v":4183.549771868792}]
[{"$f":"A13","v":"A"},{"$f":"(B37-B29)/(B37^B39)","v":9.5044721811912397E-2},9.5044721811912397E-2,{"$f":"(D37-D29)/(D37^D39)","v":9.5044721811912397E-2}]
["b1",{"$f":"0.5-(B31-B32)/(B30^2)+SQRT(((B31-B32)/(B30^2)-0.5)^2 + 2*B31/(B30^2))","v":1.254983443527075},1.254983443527075,{"$f":"0.5-(D31-D32)/(D30^2)+SQRT(((D31-D32)/(D30^2)-0.5)^2 + 2*D31/(D30^2))","v":1.254983443527075}]
[]
["                                                                                  Hedging a Perpetual American WRITTEN CALL Option (per SF)"]
["MONTH",0,{"$f":"B42+1","v":1},{"$f":"C42+1","v":2},{"$f":"D42+1","v":3},{"$f":"E42+1","v":4}]
["V",750,800,750,700,750]
["K",850,{"$f":"B44","v":850},850,850,850]
["s",0.5,0.5,0.5,0.5,0.5]
["r",0.04,0.04,0.04,0.04,0.04]
["d",0.04,0.04,0.04,0.04,0.04]
["OUTPUT"]
["WRITE ROV CALL",{"$f":"IF(B43<B57,-B58*(B43^B59),-B61)","v":-385.55140364216555},{"$f":"IF(C43<C57,-C58*(C43^C59),-C61)","v":-418.07853698190758},{"$f":"IF(D43<D57,-D58*(D43^D59),-D61)","v":-385.55140364216555},{"$f":"IF(E43<E57,-E58*(E43^E59),-E61)","v":-353.57287450837214},{"$f":"IF(F43<F57,-F58*(F43^F59),-F61)","v":-385.55140364216555}]
["HEDGE LONG VD",{"$f":"B56*B43","v":483.86062819954236},{"$f":"B50+C51","v":492.42426679301366},{"$f":"C50+D51","v":484.39585561163432},{"$f":"D50+E51","v":476.52070621367847},{"$f":"E50+F51","v":484.95836628291693}]
["ALTER HEDGE",null,{"$f":"(C56-B56)*C43","v":8.5636385934712678},{"$f":"(D56-C56)*D43","v":-8.0284111813793135},{"$f":"(E56-D56)*E43","v":-7.8751493979558713},{"$f":"(F56-E56)*F43","v":8.4376600692384347}]
["GAIN ROV",null,{"$f":"-B49+C49","v":-32.527133339742022},{"$f":"-C49+D49","v":32.527133339742022},{"$f":"-D49+E49","v":31.978529133793415},{"$f":"-E49+F49","v":-31.978529133793415}]
["GAIN V",null,{"$f":"(B50/B43)*(C43-B43)","v":32.257375213302822},{"$f":"(C50/C43)*(D43-C43)","v":-30.776516674563354},{"$f":"(D50/D43)*(E43-D43)","v":-32.293057040775622},{"$f":"(E50/E43)*(F43-E43)","v":34.03719330097703}]
["NET GAIN",null,{"$f":"C52+C53","v":-0.26975812643919994},{"$f":"D52+D53","v":1.7506166651786685},{"$f":"E52+E53","v":-0.31452790698220667},{"$f":"F52+F53","v":2.0586641671836148}]
["CUM GAIN",null,{"$f":"SUM($C$54:C54)","v":-0.26975812643919994},{"$f":"SUM($C$54:D54)","v":1.4808585387394686},{"$f":"SUM($C$54:E54)","v":1.1663306317572619},{"$f":"SUM($C$54:F54)","v":3.2249947989408767}]
["F'(V)",{"$f":"IF(B43<B57,B58*B59*(B43^(B59-1)),1)","v":0.64514750426605649},{"$f":"IF(C43<C57,C58*C59*(C43^(C59-1)),1)","v":0.65585205250789558},{"$f":"IF(D43<D57,D58*D59*(D43^(D59-1)),1)","v":0.64514750426605649},{"$f":"IF(E43<E57,E58*E59*(E43^(E59-1)),1)","v":0.63389729084040525},{"$f":"IF(F43<F57,F58*F59*(F43^(F59-1)),1)","v":0.64514750426605649}]
["V*",{"$f":"(B59/(B59-1))*B44","v":4183.549771868792},{"$f":"(C59/(C59-1))*C44","v":4183.549771868792},{"$f":"(D59/(D59-1))*D44","v":4183.549771868792},{"$f":"(E59/(E59-1))*E44","v":4183.549771868792},{"$f":"(F59/(F59-1))*F44","v":4183.549771868792}]
["A",{"$f":"(B57-B44)/(B57^B59)","v":9.5044721811912397E-2},{"$f":"(C57-C44)/(C57^C59)","v":9.5044721811912397E-2},{"$f":"(D57-D44)/(D57^D59)","v":9.5044721811912397E-2},{"$f":"(E57-E44)/(E57^E59)","v":9.5044721811912397E-2},{"$f":"(F57-F44)/(F57^F59)","v":9.5044721811912397E-2}]
["b1",{"$f":"0.5-(B46-B47)/(B45^2)+SQRT(((B46-B47)/(B45^2)-0.5)^2 + 2*B46/(B45^2))","v":1.254983443527075},{"$f":"0.5-(C46-C47)/(C45^2)+SQRT(((C46-C47)/(C45^2)-0.5)^2 + 2*C46/(C45^2))","v":1.254983443527075},{"$f":"0.5-(D46-D47)/(D45^2)+SQRT(((D46-D47)/(D45^2)-0.5)^2 + 2*D46/(D45^2))","v":1.254983443527075},{"$f":"0.5-(E46-E47)/(E45^2)+SQRT(((E46-E47)/(E45^2)-0.5)^2 + 2*E46/(E45^2))","v":1.254983443527075},{"$f":"0.5-(F46-F47)/(F45^2)+SQRT(((F46-F47)/(F45^2)-0.5)^2 + 2*F46/(F45^2))","v":1.254983443527075}]
["F''(V)",{"$f":"IF(B43<B57,B58*B59*(B59-1)*(B43^(B59-2)),0)","v":2.1933590962754321E-4},{"$f":"IF(C43<C57,C58*C59*(C59-1)*(C43^(C59-2)),0)","v":2.0903926849095414E-4},{"$f":"IF(D43<D57,D58*D59*(D59-1)*(D43^(D59-2)),0)","v":2.1933590962754321E-4},{"$f":"IF(E43<E57,E58*E59*(E59-1)*(E43^(E59-2)),0)","v":2.3090473437281488E-4},{"$f":"IF(F43<F57,F58*F59*(F59-1)*(F43^(F59-2)),0)","v":2.1933590962754321E-4}]
["V-K",{"$f":"B43-B44","v":-100},{"$f":"C43-C44","v":-50},{"$f":"D43-D44","v":-100},{"$f":"E43-E44","v":-150},{"$f":"F43-F44","v":-100}]
[null,null,null," "," "," "]
[{"$f":"A50","v":"HEDGE LONG VD"},"B56*B43","B50+C51"]
["ALTER HEDGE",null,"(C56-B56)*C43"]
["GAIN ROV",null,"-B49+C49"]
["GAIN V",null,"(B50/B43)*(C43-B43)"]
["NET GAIN",null,"C52+C53"]
["CUM GAIN",null,"SUM($C$54:C54)"]
[]
["ISSUE A PERPETUAL AMERICAN REAL CALL OPTION"]
["HEDGE BY BUYING DELTA OF V BEGINNING OF EACH MONTH."]

{"sheet":"Problem 4.6","index":18,"n_rows":74,"n_cols":7,"rows_kept":74,"cut":false,"hidden":false,"merged":[]}
["                                                       Perpetual American Option"]
["Problem 4.6","MONTH",1,2,3,4]
["V",750,800,750,700,750]
["K",850,850,850,850,850]
["s",0.5,0.5,0.5,0.5,0.5]
["r",0.04,0.04,0.02,0.05,0.04]
["d",0.04,0.04,0.04,0.04,0.04]
[]
["F(V)",{"$f":"IF(B3<B12,B13*(B3^B14),B10)","v":385.55140364216555},{"$f":"IF(C3<C12,C13*(C3^C14),C10)","v":418.07853698190758},{"$f":"IF(D3<D12,D13*(D3^D14),D10)","v":366.92206183363351},{"$f":"IF(E3<E12,E13*(E3^E14),E10)","v":361.94124131739443},{"$f":"IF(F3<F12,F13*(F3^F14),F10)","v":385.55140364216555}]
["V-K",{"$f":"B3-B4","v":-100},{"$f":"C3-C4","v":-50},{"$f":"D3-D4","v":-100},{"$f":"E3-E4","v":-150},{"$f":"F3-F4","v":-100}]
["F'(V)",{"$f":"IF(B3<B12,B13*B14*(B3^(B14-1)),1)","v":0.64514750426605649},{"$f":"IF(C3<C12,C13*C14*(C3^(C14-1)),1)","v":0.65585205250789558},{"$f":"IF(D3<D12,D13*D14*(D3^(D14-1)),1)","v":0.62844287404807353},{"$f":"IF(E3<E12,E13*E14*(E3^(E14-1)),1)","v":0.64221229872477104},{"$f":"IF(F3<F12,F13*F14*(F3^(F14-1)),1)","v":0.64514750426605649}]
["V*",{"$f":"(B14/(B14-1))*B4","v":4183.549771868792},{"$f":"(C14/(C14-1))*C4","v":4183.549771868792},{"$f":"(D14/(D14-1))*D4","v":3837.1034638421597},{"$f":"(E14/(E14-1))*E4","v":4361.6915720768257},{"$f":"(F14/(F14-1))*F4","v":4183.549771868792}]
["A",{"$f":"(B12-B4)/(B12^B14)","v":9.5044721811912397E-2},{"$f":"(C12-C4)/(C12^C14)","v":9.5044721811912397E-2},{"$f":"(D12-D4)/(D12^D14)","v":7.4369508155089262E-2},{"$f":"(E12-E4)/(E12^E14)","v":0.10589807296022138},{"$f":"(F12-F4)/(F12^F14)","v":9.5044721811912397E-2}]
["b1",{"$f":"0.5-(B6-B7)/(B5^2)+SQRT(((B6-B7)/(B5^2)-0.5)^2 + 2*B6/(B5^2))","v":1.254983443527075},{"$f":"0.5-(C6-C7)/(C5^2)+SQRT(((C6-C7)/(C5^2)-0.5)^2 + 2*C6/(C5^2))","v":1.254983443527075},{"$f":"0.5-(D6-D7)/(D5^2)+SQRT(((D6-D7)/(D5^2)-0.5)^2 + 2*D6/(D5^2))","v":1.2845565981523412},{"$f":"0.5-(E6-E7)/(E5^2)+SQRT(((E6-E7)/(E5^2)-0.5)^2 + 2*E6/(E5^2))","v":1.2420485918406858},{"$f":"0.5-(F6-F7)/(F5^2)+SQRT(((F6-F7)/(F5^2)-0.5)^2 + 2*F6/(F5^2))","v":1.254983443527075}]
[" "," "," "," "," "," "]
["SF",2000,2000,2000,2000,2000]
["TOTAL ROV",{"$f":"B9*B16","v":771102.80728433107},{"$f":"C9*C16","v":836157.07396381511},{"$f":"D9*D16","v":733844.12366726703},{"$f":"E9*E16","v":723882.48263478884},{"$f":"F9*F16","v":771102.80728433107}]
["COST",200000,200000,200000,200000,200000]
["RO WORTH",{"$f":"B17-B18","v":571102.80728433107},{"$f":"C17-C18","v":636157.07396381511},{"$f":"D17-D18","v":533844.12366726703},{"$f":"E17-E18","v":523882.48263478884},{"$f":"F17-F18","v":571102.80728433107}]
["SECURITY ANALYSIS OF CITY LTD."]
["FUNDAMENTALS",null," "," "," "," "]
["OTHER NET ASSETS",250000,{"$f":"B22","v":250000},{"$f":"C22","v":250000},{"$f":"D22","v":250000},{"$f":"E22","v":250000}]
["ROV",{"$f":"B19","v":571102.80728433107},{"$f":"C19","v":636157.07396381511},{"$f":"D19","v":533844.12366726703},{"$f":"E19","v":523882.48263478884},{"$f":"F19","v":571102.80728433107}]
["TOTAL EQUITY VALUE",{"$f":"B22+B23","v":821102.80728433107},{"$f":"C22+C23","v":886157.07396381511},{"$f":"D22+D23","v":783844.12366726703},{"$f":"E22+E23","v":773882.48263478884},{"$f":"F22+F23","v":821102.80728433107}]
["SHARES",1000000,1000000,1000000,1000000,1000000]
["NET REAL OPTION VALUE/SHARE",{"$f":"B24/B25","v":0.82110280728433105},{"$f":"C24/C25","v":0.88615707396381516},{"$f":"D24/D25","v":0.78384412366726708},{"$f":"E24/E25","v":0.77388248263478887},{"$f":"F24/F25","v":0.82110280728433105}]
["MP/SHARE",{"$f":"B26","v":0.82110280728433105},1,0.82,0.8,0.8]
["PHF SHARES",{"$f":"0.1*B25","v":100000}]
["PHF MARKET POSITION","$B$28*B27"]
["PHF MARKET POSITION",{"$f":"$B$28*B27","v":82110.280728433107},{"$f":"$B$28*C27","v":100000},{"$f":"$B$28*D27","v":82000},{"$f":"$B$28*E27","v":80000},{"$f":"$B$28*F27","v":80000}]
["PHF HEDGE: SHORT VD",{"$f":"B16*B51*0.1","v":-96772.125639908481},{"$f":"C16*C51*0.1","v":-95059.397921214229},{"$f":"D16*D51*0.1","v":-99170.774690187536},{"$f":"E16*E51*0.1","v":-97243.055235449865},{"$f":"F16*F51*0.1","v":-96802.774404257056}]
["PHF HEDGE","B16*B51*0.1"]
["GAINS/LOSSES"]
["PHF POSITION",null,{"$f":"C30-B30","v":17889.719271566893},{"$f":"D30-C30","v":-18000},{"$f":"E30-D30","v":-2000},{"$f":"F30-E30","v":0}]
["PHF HEDGE",null,{"$f":"2000*0.1*C54","v":-6451.4750426605642},{"$f":"2000*0.1*D54","v":5941.2123700758875},{"$f":"2000*0.1*E54","v":6611.3849793458339},{"$f":"2000*0.1*F54","v":-6945.9325168178484}]
[]
["CUM £ GAIN/LOSS"]
[{"$f":"A34","v":"PHF POSITION"},null,{"$f":"SUM($C$34:C34)","v":17889.719271566893},{"$f":"SUM($C$34:D34)","v":-110.28072843310656},{"$f":"SUM($C$34:E34)","v":-2110.2807284331066},{"$f":"SUM($C$34:F34)","v":-2110.2807284331066}]
[{"$f":"A35","v":"PHF HEDGE"},null,{"$f":"SUM($C$35:C35)","v":-6451.4750426605642},{"$f":"SUM($C$35:D35)","v":-510.26267258467669},{"$f":"SUM($C$35:E35)","v":6101.1223067611572},{"$f":"SUM($C$35:F35)","v":-844.81021005669118}]
["NET CUM GAIN",null,{"$f":"C38+C39","v":11438.244228906329},{"$f":"D38+D39","v":-620.54340101778325},{"$f":"E38+E39","v":3990.8415783280507},{"$f":"F38+F39","v":-2955.0909384897977}]
[" "]
[null,"                                                       Hedging a Perpetual American Option"]
["MONTH",0,{"$f":"B43+1","v":1},{"$f":"C43+1","v":2},{"$f":"D43+1","v":3},{"$f":"E43+1","v":4}]
["V",{"$f":"B3","v":750},{"$f":"C3","v":800},{"$f":"D3","v":750},{"$f":"E3","v":700},{"$f":"F3","v":750}]
["K",{"$f":"B4","v":850},{"$f":"B45","v":850},850,850,850]
["s",{"$f":"B5","v":0.5},0.5,0.5,0.5,0.5]
["r",{"$f":"B6","v":0.04},{"$f":"C6","v":0.04},{"$f":"D6","v":0.02},{"$f":"E6","v":0.05},{"$f":"F6","v":0.04}]
["d",{"$f":"B7","v":0.04},{"$f":"B48","v":0.04},{"$f":"C48","v":0.04},{"$f":"D48","v":0.04},{"$f":"E48","v":0.04}]
["OUTPUT"]
["ROV",{"$f":"IF(B44<B58,B59*(B44^B60),B62)","v":385.55140364216555},{"$f":"IF(C44<C58,C59*(C44^C60),C62)","v":418.07853698190758},{"$f":"IF(D44<D58,D59*(D44^D60),D62)","v":366.92206183363351},{"$f":"IF(E44<E58,E59*(E44^E60),E62)","v":361.94124131739443},{"$f":"IF(F44<F58,F59*(F44^F60),F62)","v":385.55140364216555}]
["HEDGE",{"$f":"-B57*B44","v":-483.86062819954236},{"$f":"B51+C52","v":-475.29698960607107},{"$f":"C51+D52","v":-495.85387345093761},{"$f":"D51+E52","v":-486.21527617724934},{"$f":"E51+F52","v":-484.01387202128524}]
["ALTER HEDGE",null,{"$f":"(C57-B57)*C44","v":8.5636385934712678},{"$f":"(D57-C57)*D44","v":-20.556883844866537},{"$f":"(E57-D57)*E44","v":9.638597273688255},{"$f":"(F57-E57)*F44","v":2.2014041559640929}]
["GAIN ROV",null,{"$f":"-B50+C50","v":32.527133339742022},{"$f":"-C50+D50","v":-51.156475148274069},{"$f":"-D50+E50","v":-4.9808205162390777},{"$f":"-E50+F50","v":23.610162324771125}]
["GAIN V",null,{"$f":"-(B51/B44)*(-C44+B44)","v":-32.257375213302822},{"$f":"-(C51/C44)*(-D44+C44)","v":29.706061850379438},{"$f":"-(D51/D44)*(-E44+D44)","v":33.056924896729171},{"$f":"-(E51/E44)*(-F44+E44)","v":-34.72966258408924}]
["NET GAIN",null,{"$f":"C53+C54","v":0.26975812643919994},{"$f":"D53+D54","v":-21.450413297894631},{"$f":"E53+E54","v":28.076104380490094},{"$f":"F53+F54","v":-11.119500259318116}]
["CUM GAIN",null,{"$f":"SUM($C$55:C55)","v":0.26975812643919994},{"$f":"SUM($C$55:D55)","v":-21.180655171455431},{"$f":"SUM($C$55:E55)","v":6.8954492090346626},{"$f":"SUM($C$55:F55)","v":-4.2240510502834532}]
["F'(V)",{"$f":"IF(B44<B58,B59*B60*(B44^(B60-1)),1)","v":0.64514750426605649},{"$f":"IF(C44<C58,C59*C60*(C44^(C60-1)),1)","v":0.65585205250789558},{"$f":"IF(D44<D58,D59*D60*(D44^(D60-1)),1)","v":0.62844287404807353},{"$f":"IF(E44<E58,E59*E60*(E44^(E60-1)),1)","v":0.64221229872477104},{"$f":"IF(F44<F58,F59*F60*(F44^(F60-1)),1)","v":0.64514750426605649}]
["V*",{"$f":"(B60/(B60-1))*B45","v":4183.549771868792},{"$f":"(C60/(C60-1))*C45","v":4183.549771868792},{"$f":"(D60/(D60-1))*D45","v":3837.1034638421597},{"$f":"(E60/(E60-1))*E45","v":4361.6915720768257},{"$f":"(F60/(F60-1))*F45","v":4183.549771868792}]
["A",{"$f":"(B58-B45)/(B58^B60)","v":9.5044721811912397E-2},{"$f":"(C58-C45)/(C58^C60)","v":9.5044721811912397E-2},{"$f":"(D58-D45)/(D58^D60)","v":7.4369508155089262E-2},{"$f":"(E58-E45)/(E58^E60)","v":0.10589807296022138},{"$f":"(F58-F45)/(F58^F60)","v":9.5044721811912397E-2}]
["b1",{"$f":"0.5-(B47-B48)/(B46^2)+SQRT(((B47-B48)/(B46^2)-0.5)^2 + 2*B47/(B46^2))","v":1.254983443527075},{"$f":"0.5-(C47-C48)/(C46^2)+SQRT(((C47-C48)/(C46^2)-0.5)^2 + 2*C47/(C46^2))","v":1.254983443527075},{"$f":"0.5-(D47-D48)/(D46^2)+SQRT(((D47-D48)/(D46^2)-0.5)^2 + 2*D47/(D46^2))","v":1.2845565981523412},{"$f":"0.5-(E47-E48)/(E46^2)+SQRT(((E47-E48)/(E46^2)-0.5)^2 + 2*E47/(E46^2))","v":1.2420485918406858},{"$f":"0.5-(F47-F48)/(F46^2)+SQRT(((F47-F48)/(F46^2)-0.5)^2 + 2*F47/(F46^2))","v":1.254983443527075}]
["F''(V)",{"$f":"IF(B44<B58,B59*B60*(B60-1)*(B44^(B60-2)),0)","v":2.1933590962754321E-4},{"$f":"IF(C44<C58,C59*C60*(C60-1)*(C44^(C60-2)),0)","v":2.0903926849095414E-4},{"$f":"IF(D44<D58,D59*D60*(D60-1)*(D44^(D60-2)),0)","v":2.3843675516293353E-4},{"$f":"IF(E44<E58,E59*E60*(E60-1)*(E44^(E60-2)),0)","v":2.2206654652728676E-4},{"$f":"IF(F44<F58,F59*F60*(F60-1)*(F44^(F60-2)),0)","v":2.1933590962754321E-4}]
["V-K",{"$f":"B44-B45","v":-100},{"$f":"C44-C45","v":-50},{"$f":"D44-D45","v":-100},{"$f":"E44-E45","v":-150},{"$f":"F44-F45","v":-100}]
[null,null,null," "," "," "]
[{"$f":"A51","v":"HEDGE"},"-B57*B44","B51+C52"]
["ALTER HEDGE",null,"(C57-B57)*C44"]
["GAIN ROV",null,"-B50+C50"]
["GAIN V",null,"-(B51/B44)*(-C44+B44)"]
["NET GAIN",null,"C53+C54"]
["CUM GAIN",null,"SUM($C$55:C55)"]
[]
["BUY A FIRM OWNING A PERPETUAL AMERICAN REAL CALL OPTION"]
["HEDGE BY SHORTING DELTA OF V BEGINNING OF EACH MONTH.",null,null,null,null,null,"RISK"]
["RISK BUYING CITY SHS",null,{"$f":"C34/$B$30","v":0.21787429190181845},-0.21921737254232732,-2.4357485838036368E-2,0,{"$f":"STDEV(C73:F73)","v":0.17884222174223935}]
["RISK CITY SHS HEDGED",null,-7.8570855993024893E-2,7.2356498082444959E-2,8.0518358002111268E-2,-8.4592726455173531E-2,{"$f":"STDEV(C74:F74)","v":9.1326361835198877E-2}]

{"sheet":"Exercise 5.1","index":19,"n_rows":27,"n_cols":3,"rows_kept":27,"cut":false,"hidden":false,"merged":[]}
[null,"                           American Perpetual Exchange Option"]
["INPUT"," ","Exercise 5.1"]
["V",300]
["K",273]
["sV",0.2]
["sk",0.2]
["dV",0.04]
["dk",0.04]
["r",0.5]
["OUTPUT"]
["F(V,K)=W(Z)",{"$f":"IF(B17<B16,B13*B4*(B17^B14),B12)","v":82.417582417582452},"IF(B17<B16,B4*B13*(B17^B14),B12)"]
["V-K",{"$f":"B3-B4","v":27},"B3-B4"]
["A",{"$f":"(B16-1)/(B16^B14)","v":0.25000000000000006},"(B16-1)/(B16^B14)"]
["b1",{"$f":"0.5-(B8-B7)/(B15^2)+SQRT(((B8-B7)/(B15^2)-0.5)^2 + (2*B8)/(B15^2))","v":1.9999999999999998},"0.5-(B8-B7)/(B15^2)+SQRT(((B8-B7)/(B15^2)-0.5)^2 + (2*B8)/(B15^2))"]
["h",{"$f":"SQRT(B5^2+B6^2-2*B9*B5*B6)","v":0.2},"SQRT(B5^2+B6^2-2*B9*B5*B6)"]
["Z*",{"$f":"(B14/(B14-1))","v":2.0000000000000004},"(B14/(B14-1))"]
["Z",{"$f":"B3/B4","v":1.098901098901099},"B3/B4"]
["ODE",{"$f":"0.5*(B15^2)*(B17^2)*B20+(B8-B7)*B17*B19-B8*B11","v":0},"0.5*(B15^2)*(B17^2)*B20+(B8-B7)*B17*B19-B8*B11"]
["W'(Z)",{"$f":"B4*B13*B14*(B17^(B14-1))","v":150},"B4*B13*B14*(B17^(B14-1))"]
["W''(Z)",{"$f":"B4*B13*B14*(B14-1)*(B17^(B14-2))","v":136.49999999999997},"B4*B13*B14*(B14-1)*(B17^(B14-2))"]
["W(Z*)",{"$f":"B13*B4*((B16^B14))","v":273.00000000000011},"B13*B4*((B16^B14))"]
["W(Z*)",{"$f":"(B16-1)*B4","v":273.00000000000011},"(B16-1)*B4"]
["W'(Z*)",{"$f":"1","v":1}," "]
["W(Z)",{"$f":"B4*(B16-1)*((B17/B16)^B14)","v":82.417582417582452},"B4*(B16-1)*((B17/B16)^B14)"]
["Sq Feet",3000]
["ROV",{"$f":"B25*B11","v":247252.74725274736},"B25*B11"]
["Z*",{"$f":"B25*B16*B4","v":1638000.0000000002},"B25*B16*B4"]

{"sheet":"Exercise 5.2","index":20,"n_rows":22,"n_cols":3,"rows_kept":22,"cut":false,"hidden":false,"merged":[]}
[null,"                           American Perpetual Exchange Option"]
["Exercise 5.2"," "]
["V",300000000]
["K",300000000]
["sV",1.1200000000000001]
["sK",0.95]
["dV",0.02]
["dK",0.02]
["r",0]
["OUTPUT"]
["W(V,K)",{"$f":"IF(B17<B16,B4*(B16-1)*(B17/B16)^B14,B13)","v":273813887.03764677},"IF(B17<B16,B4*(B16-1)*(B17/B16)^B14,B13)"]
["V-K",{"$f":"B3-B4","v":0},"B3-B4"]
["A",{"$f":"(B16-1)/(B16^B14)","v":0.91271295679215581},"(B16-1)/(B16^B14)"]
["b1",{"$f":"0.5-(B8-B7)/(B15^2)+SQRT(((B8-B7)/(B15^2)-0.5)^2 + (2*B8)/(B15^2))","v":1.0182134060600989},"0.5-(B8-B7)/(B15^2)+SQRT(((B8-B7)/(B15^2)-0.5)^2 + (2*B8)/(B15^2))"]
["h",{"$f":"SQRT(B5^2+B6^2-2*B9*B5*B6)","v":1.468638825579659},"SQRT(B5^2+B6^2-2*B9*B5*B6)"]
["z*",{"$f":"(B14/(B14-1))","v":55.904612388275559},"(B14/(B14-1))"]
["z",{"$f":"B3/B4","v":1},"B3/B4"]
[]
["THE OPTION TO ACQUIRE IS WORTH $273 MILLION, DUE TO THE HIGH"]
["VOLALILITY OF BOTH COMPANIES.  DON'T MAKE THE ACQUISITION"]
["UNTIL BLOCKBUSTER IS WORTH $16770 MILLION (WHILE BIOSURGERY"]
["IS WORTH THE CURRENT VALUE OF $300 MILLION)."]

{"sheet":"Exercise 5.3","index":21,"n_rows":27,"n_cols":3,"rows_kept":27,"cut":false,"hidden":false,"merged":[]}
["American Multi-factor Perpetual Real Option"]
["Exercise 5.3","Stochastic P & Q"]
["DSP",1]
["DS",2]
["X",{"$f":"B3*B4","v":2},"B3*B4"]
["K",{"$f":"100*B4","v":200},"100*B4"]
["s",0.2]
["a",0.2]
["r",-0.5]
["r",0.04]
["m",0.01]
["w",0.01]
["OUTPUT"]
["n",{"$f":"B10-B11-B12","v":1.9999999999999997E-2},"B10-B11-B12"]
["V",{"$f":"B5/B14","v":100.00000000000001},"B5/B14"]
["P(P,Q)",{"$f":"IF(B5<B20,(B6/(B18-1))*((B5/B20)^B18),B17)","v":12.500000000000005},"IF(B5<B20,(B6/(B18-1))*((B5/B20)^B18),B17)"]
["V-K",{"$f":"B15-B6","v":-99.999999999999986},"B15-B6"]
["b1",{"$f":"(1/B19^2)*(-(B9*B7*B8+B11+B12-0.5*(B19^2))+SQRT((B9*B7*B8+B11+B12-0.5*(B19^2))^2+(2*B10)*(B19^2)))","v":2}]
["z",{"$f":"SQRT(B7^2+B8^2+2*B9*B7*B8)","v":0.2},"SQRT(B7^2+B8^2+2*B9*B7*B8)"]
["X*",{"$f":"B6*B14*(B18/(B18-1))","v":7.9999999999999991},"B6*B14*(B18/(B18-1))"]
["b1","(1/B19^2)*(-(B9*B7*B8+B11+B12-0.5*(B19^2))+SQRT((B9*B7*B8+B11+B12-0.5*(B19^2))^2+(2*B10)*(B19^2)))"]
["V*-K",{"$f":"B20/B14-B6","v":200},"B20/B14-B6"]
[]
["THE VALUE OF THIS OPPORTUNITY IS 12.50, EVEN THOUGH"]
["THE NET PRESENT VALUE IS VERY NEGATIVE."]
["ROGroupie SHOULD NOT START THIS VENTURE UNTIL"]
["DS*DSP ARE OVER FOUR TIMES CURRENT EXPECTATIONS."]

{"sheet":"Problem 5.4","index":22,"n_rows":24,"n_cols":3,"rows_kept":24,"cut":false,"hidden":false,"merged":[]}
[null,"                           American Perpetual Exchange Option"]
["Problem 5.4"," "]
["V",300]
["K",273]
["sV",0.3]
["sK",0.2]
["dV",0.03]
["dK",0.02]
["r",0]
["SQ FT",3000]
["W(V,K)",{"$f":"IF(B17<B16,B4*(B16-1)*(B17/B16)^B14,B13)","v":138.4834490547459},"IF(B17<B16,B4*(B16-1)*(B17/B16)^B14,B13)*B10"]
["V-K",{"$f":"(B3-B4)","v":27},"(B3-B4)*B10"]
["A",{"$f":"(B16-1)/(B16^B14)","v":0.44547644219851407},"(B16-1)/(B16^B14)"]
["b1",{"$f":"0.5-(B8-B7)/(B15^2)+SQRT(((B8-B7)/(B15^2)-0.5)^2 + (2*B8)/(B15^2))","v":1.3772558479494157},"0.5-(B8-B7)/(B15^2)+SQRT(((B8-B7)/(B15^2)-0.5)^2 + (2*B8)/(B15^2))"]
["h",{"$f":"SQRT(B5^2+B6^2-2*B9*B5*B6)","v":0.36055512754639896},"SQRT(B5^2+B6^2-2*B9*B5*B6)"]
["z*",{"$f":"(B14/(B14-1))","v":3.6507210038904021},"(B14/(B14-1))"]
["z",{"$f":"B3/B4","v":1.098901098901099},"B3/B4"]
["ROV",{"$f":"B11*B10","v":415450.3471642377},"B11*B10"]
["THE OPTION TO BUILD IS WORTH OVER £415,000, DUE TO THE LOW CORRELATION"]
["OF VALUE AND CONSTRUCTION COSTS.  DON'T START CONSTRUCTION"]
["UNTIL HOUSE VALUE IS WORTH £2,989,350 (EVEN THOUGH THE "]
["CURRENT INVESTMENT NET PRESENT VALUE IS POSITIVE). NOTE THAT THE"]
["OPTION TO BUILD IS WORTH ALMOST HALF THE CURRENT GROSS VALUE"]
["OF THE HOUSE WERE IT TO BE BUILT TODAY."]

{"sheet":"Problem 5.5","index":23,"n_rows":25,"n_cols":3,"rows_kept":25,"cut":false,"hidden":false,"merged":[]}
[null,"                           American Perpetual Exchange Option"]
["Problem 5.5"," "]
["V",{"$f":"20*50/B7","v":50000}]
["K",25000]
["sV",0.2]
["sK",0.2]
["dV",0.02]
["dK",0.02]
["r",-0.5]
[" "," "]
["W(V,K)",{"$f":"IF(B17<B16,B4*(B16-1)*(B17/B16)^B14,B13)","v":31421.51280659383},"IF(B17<B16,B4*(B16-1)*(B17/B16)^B14,B13)"]
["V-K",{"$f":"(B3-B4)","v":25000},"B3-B4"]
["A",{"$f":"(B16-1)/(B16^B14)","v":0.52342760982484893},"(B16-1)/(B16^B14)"]
["b1",{"$f":"0.5-(B8-B7)/(B15^2)+SQRT(((B8-B7)/(B15^2)-0.5)^2 + (2*B8)/(B15^2))","v":1.2637626158259732},"0.5-(B8-B7)/(B15^2)+SQRT(((B8-B7)/(B15^2)-0.5)^2 + (2*B8)/(B15^2))"]
["h",{"$f":"SQRT(B5^2+B6^2-2*B9*B5*B6)","v":0.34641016151377552},"SQRT(B5^2+B6^2-2*B9*B5*B6)"]
["z*",{"$f":"(B14/(B14-1))","v":4.7912878474779221},"(B14/(B14-1))"]
["z",{"$f":"B3/B4","v":2},"B3/B4"]
[]
["THE INVESTMENT OPTION IS WORTH $31,422 DUE TO THE HIGH VOLATILITY"]
["OF VALUE AND COSTS, EVEN THOUGH THE NPV IS $25,000.  YM MA"]
["SHOULD TAKE UP THE OPTION ESPECIALLY IF THERE IS NO PREMIUM"]
["BUT NOT CONSTRUCT THE GENERATING PLANT "]
["UNTIL V RISES TO $119750 (PERHAPS DUE TO HIGHER "]
["ELECTRICITY PRICES) OR THE COST OF THE PLANT FALLS."]
["THERE IS AN IMPLICIT ASSUMPTION THAT YM MA WILL LIVE FOREVER."]

{"sheet":"Problem 5.6","index":24,"n_rows":21,"n_cols":3,"rows_kept":21,"cut":false,"hidden":false,"merged":[]}
[null,"                           American Perpetual Exchange Option"]
["Problem 5.6"," "]
["V",{"$f":"'Problem 5.5'!B3","v":50000}]
["K",10435.603890287959]
["sV",{"$f":"'Problem 5.5'!B5","v":0.2}]
["sK",{"$f":"'Problem 5.5'!B6","v":0.2}]
["dV",{"$f":"'Problem 5.5'!B7","v":0.02}," "]
["dK",{"$f":"'Problem 5.5'!B8","v":0.02}]
["r",{"$f":"'Problem 5.5'!B9","v":-0.5}]
[" "," "]
["W(V,K)",{"$f":"IF(B17<B16,B4*(B16-1)*(B17/B16)^B14,B13)","v":0.52342760982484893},"IF(B17<B16,B4*(B16-1)*(B17/B16)^B14,B13)"]
["V-K",{"$f":"(B3-B4)","v":39564.396109712041},"B3-B4"]
["A",{"$f":"(B16-1)/(B16^B14)","v":0.52342760982484893},"(B16-1)/(B16^B14)"]
["b1",{"$f":"0.5-(B8-B7)/(B15^2)+SQRT(((B8-B7)/(B15^2)-0.5)^2 + (2*B8)/(B15^2))","v":1.2637626158259732},"0.5-(B8-B7)/(B15^2)+SQRT(((B8-B7)/(B15^2)-0.5)^2 + (2*B8)/(B15^2))"]
["h",{"$f":"SQRT(B5^2+B6^2-2*B9*B5*B6)","v":0.34641016151377552},"SQRT(B5^2+B6^2-2*B9*B5*B6)"]
["z*",{"$f":"(B14/(B14-1))","v":4.7912878474779221},"(B14/(B14-1))"]
["z",{"$f":"B3/B4","v":4.7912895626992125},"B3/B4"]
["z*-z",{"$f":"B16-B17","v":-1.7152212903326358E-6}]
["WHAT INVESTMENT COST SUBSIDY WOULD BRING Z TO 4.79?"]
["USE SOLVER","SET B18, Changing B4."]
["Subsidy",{"$f":"25000-B4","v":14564.396109712041}]

{"sheet":"Exercise 10.1","index":25,"n_rows":22,"n_cols":4,"rows_kept":22,"cut":false,"hidden":false,"merged":[]}
[null,"Real Debt Strategy Simple"]
["Exercise10.1"]
[" "]
[null,"ACCOUNTS","REAL"]
["ASSETS",{"$f":"B10","v":100},{"$f":"C10-C15*C19*(C19/C10)^-C20","v":87.5},"C10-C15*C19*(C19/C10)^-C20"]
["DEBT",{"$f":"B12","v":100},{"$f":"B6+((1-C15)*C19-B6)*(C19/C10)^-C20","v":62.5},"B6+((1-C15)*C19-B6)*(C19/C10)^-C20"]
["EQ",{"$f":"B5-B6","v":0},{"$f":"C5-C6","v":25},"C5-C6"]
[]
["INPUT"," "]
["NOM V",100,{"$f":"B10","v":100}," "]
["Coupon",4,{"$f":"B11","v":4}," "]
["NOM Debt",{"$f":"B11/C17","v":100},{"$f":"B12","v":100}," "]
["d",null,0.04," "]
["s",null,0.2," "]
["a",null,0.5," "]
["t",null,0," "]
["r(f)",null,0.04," "]
["OUTPUT"]
["VB   ",null,{"$f":"((((1-C16)*C11)/C17))*-C20/(1-C20)","v":49.999999999999993},"(1-C16)*(C11/C17)*(-C20/(-C20+1))"]
["b2",null,{"$f":"(0.5-(C17-C13)/(C14^2)-SQRT((0.5-(C17-C13)/(C14^2))^2+2*C17/(C14^2)))","v":-0.99999999999999978}," "]
[null,null," "]
["b2","(0.5-(C17-C13)/(C14^2)-SQRT((0.5-(C17-C13)/(C14^2))^2+2*C17/(C14^2)))"]

{"sheet":"Exercise 10.2","index":26,"n_rows":22,"n_cols":4,"rows_kept":22,"cut":false,"hidden":false,"merged":[]}
[null,"Real Debt Strategy Simple"]
["Exercise10.2"]
[" "]
[null,"ACCOUNTS","REAL"]
["ASSETS",{"$f":"B10","v":100},{"$f":"C10-C15*C19*(C19/C10)^-C20","v":85.18518518518519},"C10-C15*C19*(C19/C10)^-C20"]
["DEBT",100,{"$f":"B6+((1-C15)*C19-B6)*(C19/C10)^-C20","v":70.370370370370381},"B6+((1-C15)*C19-B6)*(C19/C10)^-C20"]
["EQ",{"$f":"B5-B6","v":0},{"$f":"C5-C6","v":14.81481481481481},"C5-C6"]
[]
["INPUT"," "]
["NOM V",100,{"$f":"B10","v":100}," "]
["Coupon",8,{"$f":"B11","v":8}," "]
["NOM Debt",{"$f":"B11/C17","v":100},{"$f":"B12","v":100}," "]
["d",null,0.06," "]
["s",null,0.2," "]
["a",null,0.5," "]
["t",null,0," "]
["r(f)",null,0.08," "]
["OUTPUT"]
["VB   ",null,{"$f":"((((1-C16)*C11)/C17))*-C20/(1-C20)","v":66.666666666666657},"(1-C16)*(C11/C17)*(-C20/(-C20+1))"]
["b2",null,{"$f":"(0.5-(C17-C13)/(C14^2)-SQRT((0.5-(C17-C13)/(C14^2))^2+2*C17/(C14^2)))","v":-1.9999999999999998}," "]
[null,null," "]
["b2","(0.5-(C17-C13)/(C14^2)-SQRT((0.5-(C17-C13)/(C14^2))^2+2*C17/(C14^2)))"]

{"sheet":"Exercise 10.3","index":27,"n_rows":22,"n_cols":4,"rows_kept":22,"cut":false,"hidden":false,"merged":[]}
[null,"Real Debt Strategy Simple"]
["Exercise10.3"]
[" "]
[null,"ACCOUNTS","REAL"]
["ASSETS",{"$f":"B10","v":200},{"$f":"C10-C15*C19*(C19/C10)^-C20","v":193.75},"C10-C15*C19*(C19/C10)^-C20"]
["DEBT",100,{"$f":"B6+((1-C15)*C19-B6)*(C19/C10)^-C20","v":81.25},"B6+((1-C15)*C19-B6)*(C19/C10)^-C20"]
["EQ",{"$f":"B5-B6","v":100},{"$f":"C5-C6","v":112.5},"C5-C6"]
[]
["INPUT"," "]
["NOM V",200,{"$f":"B10","v":200}," "]
["Coupon",4,{"$f":"B11","v":4}," "]
["NOM Debt",{"$f":"B11/C17","v":100},{"$f":"B12","v":100}," "]
["d",null,0.04," "]
["s",null,0.2," "]
["a",null,0.5," "]
["t",null,0," "]
["r(f)",null,0.04," "]
["OUTPUT"]
["VB   ",null,{"$f":"((((1-C16)*C11)/C17))*-C20/(1-C20)","v":49.999999999999993},"(1-C16)*(C11/C17)*(-C20/(-C20+1))"]
["b2",null,{"$f":"(0.5-(C17-C13)/(C14^2)-SQRT((0.5-(C17-C13)/(C14^2))^2+2*C17/(C14^2)))","v":-0.99999999999999978}," "]
[null,null," "]
["b2","(0.5-(C17-C13)/(C14^2)-SQRT((0.5-(C17-C13)/(C14^2))^2+2*C17/(C14^2)))"]

{"sheet":"Problem 10.4","index":28,"n_rows":22,"n_cols":4,"rows_kept":22,"cut":false,"hidden":false,"merged":[]}
[null,"Real Debt Strategy Simple"]
["Problem10.4"]
[" "]
[null,"ACCOUNTS","REAL"]
["ASSETS",{"$f":"B10","v":100},{"$f":"C10+(C16*C11/C17)*(1-(C19/C10)^-C20)-C15*C19*(C19/C10)^-C20","v":115.45606441432012},"C10+(C16*C11/C17)*(1-(C19/C10)^-C20)-C15*C19*(C19/C10)^-C20"]
["DEBT",{"$f":"B11/C17","v":100},{"$f":"B6+((1-C15)*C19-B6)*(C19/C10)^-C20","v":62.203413920870183},"B6+((1-C15)*C19-B6)*(C19/C10)^-C20"]
["EQ",{"$f":"B5-B6","v":0},{"$f":"C5-C6","v":53.252650493449941},"C5-C6"]
[]
["INPUT"," "]
["NOM V",100,{"$f":"B10","v":100}," "]
["Coupon",4,{"$f":"B11","v":4}," "]
["NOM Debt",{"$f":"B11/C17","v":100},{"$f":"B12","v":100}," "]
["d",null,0.04," "]
["s",null,0.3," "]
["a",null,0.4," "]
["t",null,0.35," "]
["r(f)",null,0.04," "]
["OUTPUT"]
["VB   ",null,{"$f":"(1-C16)*(C11/C17)*(-C20/(-C20+1))","v":23.524423356287159},"(1-C16)*(C11/C17)*(-C20/(-C20+1))"]
["b2",null,{"$f":"(0.5-(C17-C13)/(C14^2)-SQRT((0.5-(C17-C13)/(C14^2))^2+2*C17/(C14^2)))","v":-0.56718737290547483}," "]
[null,null," "]
["b2","(0.5-(C17-C13)/(C14^2)-SQRT((0.5-(C17-C13)/(C14^2))^2+2*C17/(C14^2)))"]

{"sheet":"Problem 10.5","index":29,"n_rows":25,"n_cols":5,"rows_kept":25,"cut":false,"hidden":false,"merged":[]}
[null,"Real Debt Strategy ",null," "]
[" ","Problem 10.5"]
["BASE CASE"]
[null,"ACCOUNTS","REAL 1","REAL 2"]
["ASSETS",{"$f":"B10","v":100},{"$f":"C10+(C16*C11/C17)*(1-(C19/C10)^-C20)-C15*C19*(C19/C10)^-C20","v":118.53969609437975},{"$f":"D10+(D16*D11/D17)*(1-(D19/D10)^-D20)-D15*D19*(D19/D10)^-D20","v":122.71507520194902},"V*"]
["DEBT",{"$f":"B11/C17","v":133.33333333333334},{"$f":"(C11/C17)+((1-C15)*C19-(C11/C17))*(C19/C10)^-C20","v":90.334942442418878},{"$f":"(D11/D17)+((1-D15)*D19-(D11/D17))*(D19/D10)^-D20","v":94.701734196691973},"D*"]
["EQ",{"$f":"B5-B6","v":-33.333333333333343},{"$f":"C5-C6","v":28.204753651960871},{"$f":"D5-D6","v":28.013341005257047},"E*"]
[null,null,"WE CHOOSE","YOU CHOOSE"]
["Retained Equity","MAX",{"$f":"C7","v":28.204753651960871},{"$f":"(1-D24)*D7","v":28.013341005257047}]
["NOM V",100,{"$f":"B10","v":100},100]
["Coupon",8,8,8]
["NOM Debt",{"$f":"B11/C17","v":133.33333333333334},133.33333333333334,133.33333333333334]
["d",null,0.06,{"$f":"C13","v":0.06}]
["s",null,0.2,{"$f":"C14","v":0.2}]
["a",null,0.5,{"$f":"C15","v":0.5}]
["t",null,0.35,{"$f":"C16","v":0.35}]
["r(f)",null,0.06,{"$f":"C17","v":0.06}]
["OUTPUT"]
["VB   ",null,{"$f":"(1-C16)*(C11/C17)*(-C20/(-C20+1))","v":49.030926021075707},{"$f":"$B$6/D23","v":44.44444444444445}]
["b2",null,{"$f":"(0.5-(C17-C13)/(C14^2)-SQRT((0.5-(C17-C13)/(C14^2))^2+2*C17/(C14^2)))","v":-1.3027756377319943},{"$f":"(0.5-(D17-D13)/(D14^2)-SQRT((0.5-(D17-D13)/(D14^2))^2+2*D17/(D14^2)))","v":-1.3027756377319943}]
["LTV",{"$f":"$B$6/B5","v":1.3333333333333335},{"$f":"$B$6/C19","v":2.7193721219138438},{"$f":"$B$6/D19","v":3}]
[]
[null," ","LTV MAX",3]
[null,null,"SHARED EQUITY",0]
["DEBT* + SH EQ"," ",{"$f":"C6","v":90.334942442418878},{"$f":"D6","v":94.701734196691973}]

{"sheet":"Problem 10.6","index":30,"n_rows":29,"n_cols":10,"rows_kept":29,"cut":false,"hidden":false,"merged":[]}
[null,null,null," ",null,"Real Debt Strategy i"]
["Problem 10.6"]
["BASE CASE",null,null,null," "]
[null,"ACCOUNTS","REAL 1","REAL 2","REAL 3","REAL 4","REAL 5","REAL 6","REAL 7"]
["ASSETS",{"$f":"B10","v":40},{"$f":"C10+(C16*C11/C17)*(1-(C19/C10)^-C20)-C15*C19*(C19/C10)^-C20","v":36.253880774474801},{"$f":"D10+(D16*D11/D17)*(1-(D19/D10)^-D20)-D15*D19*(D19/D10)^-D20","v":43.560841707710097},42.860126272246134,41.8615578499656,41.06040097752981,39.916022285668191,38.995674631318138,"V*"]
["DEBT",{"$f":"B11/C17","v":66.666666666666671},{"$f":"(C11/C17)+((1-C15)*C19-(C11/C17))*(C10^C20)*C19^-C20","v":34.668605538403241},{"$f":"(D11/D17)+((1-D15)*D19-(D11/D17))*(D10^D20)*D19^-D20","v":42.416793371007479},41.630896053093608,40.528300249586508,39.657731440960944,38.434740698121963,37.467828710203079,"D*"]
["EQ",{"$f":"B5-B6","v":-26.666666666666671},{"$f":"C10-(1-C16)*(C11/C17)+((1-C16)*(C11/C17)-C19)*((C19/C10)^-C20)","v":1.5852752360715421},{"$f":"D10-(1-D16)*(D11/D17)+((1-D16)*(D11/D17)-D19)*((D19/D10)^-D20)","v":1.1440483367026362},1.2292302191525302,1.3332576003790733,1.4026695365688706,1.4812815875462402,1.527845921115043,"E*"]
[null,null,"WE CHOOSE","YOU CHOOSE","NEGOTIATE","NEGOTIATE","NEGOTIATE","NEGOTIATE","NEGOTIATE"]
["Retained Equity",null,{"$f":"C7","v":1.5852752360715421},{"$f":"(1-D23)*D7","v":1.1440483367026362},1.2169379169610048,1.3065924483714917,1.3605894504718044,1.4220303240443906,1.4514536250592909]
["NOM V",40,{"$f":"B10","v":40},{"$f":"C10","v":40},{"$f":"D10","v":40},40,{"$f":"B10","v":40},40,40]
["Coupon",4,{"$f":"B11","v":4},{"$f":"C11","v":4},4,4,4,4,4]
["NOM Debt",{"$f":"B11/C17","v":66.666666666666671},66.666666666666671,{"$f":"C12","v":66.666666666666671},66.666666666666671,66.666666666666671,66.666666666666671,66.666666666666671,66.666666666666671]
["d",null,0.01,{"$f":"C13","v":0.01},0.01,0.01,0.01,0.01,0.01]
["s",null,0.2,{"$f":"C14","v":0.2},0.2,0.2,0.2,0.2,0.2]
["a",null,0.5,{"$f":"C15","v":0.5},0.5,0.5,0.5,0.5,0.5]
["t",null,0.3,0.3,0.3,0.3,0.3,0.3,0.3]
["r(f)",null,0.06,0.06,0.06,0.06,0.06,0.06,0.06]
["OUTPUT",null,null," "]
["VB   ",null,{"$f":"(1-C16)*(C11/C17)*(-C20/(-C20+1))","v":33.837196510349166},{"$f":"$B$6/D22","v":30.03003003003003},{"$f":"$B$6/E22","v":30.441400304414007},31.007751937984498,31.446540880503147,32.051282051282051,32.520325203252035]
["b2",null,{"$f":"(0.5-(C17-C13)/(C14^2)-SQRT((0.5-(C17-C13)/(C14^2))^2+2*C17/(C14^2)))","v":-2.6374586088176866},{"$f":"(0.5-(D17-D13)/(D14^2)-SQRT((0.5-(D17-D13)/(D14^2))^2+2*D17/(D14^2)))","v":-2.6374586088176866},-2.6374586088176866,-2.6374586088176866,-2.6374586088176866,-2.6374586088176866,-2.6374586088176866]
["LTV",{"$f":"$B$12/B5","v":1.6666666666666667},{"$f":"$B$12/C5","v":1.8388835965280865},{"$f":"$B$12/D5","v":1.5304265035555302},1.555447276174647,1.592551020332404,1.6236243455866353,1.6701730996528497,1.7095913148563777]
[null,null,"LTV MAX",2.2200000000000002,2.19,2.15,2.12,2.08,2.0499999999999998]
[null,null,"SHARED EQUITY",0,0.01,{"$f":"E23+0.01","v":0.02},{"$f":"F23+0.01","v":0.03},0.04,0.05]
["DEBT LOSS + SH EQ",{"$f":"$B$6-(1-C15)*B10","v":46.666666666666671},{"$f":"$B$6-C6","v":31.99806112826343},{"$f":"$B$6-D6+D23*D7","v":24.249873295659192},{"$f":"$B$6-E6+E23*E7","v":25.048062915764589},26.165031569087745,27.051015311802793,28.291177232046557,29.275230252519343]
["CHANGE DEBT LOSS",null,{"$f":"C24-$B$24","v":-14.668605538403241},-22.416793371007479,-21.618603750902082,-20.501635097578927,-19.615651354863878,-18.375489434620114,-17.391436414147329]
["LTV",null,null,"$B$12/D5"]
["VB   ",null,null,"$B$6/D22"]
["DEBT LOSS + SH EQ",null,"$B$6-C6","$B$6-D6+D23*D7"]
["CHANGE DEBT LOSS",null,"C24-$B$24"]

{"sheet":"Exercise 12.1","index":31,"n_rows":59,"n_cols":7,"rows_kept":59,"cut":false,"hidden":false,"merged":[]}
["Continuous American Perpetual SINGLE SWITCH Option"]
["ONE WAY SWITCH FROM OUTPUT x TO y ",null," ","EXERCISE 12.1"]
["OUTPUT x RO","x",1," "]
["OUTPUT y IA","y",1," "]
["Convenience yield of x","δx",0.05]
["Convenience yield of y","δy",0.05]
["Volatility of x","σx",0.1]
["Volatility of y","σy",0.1]
["Correlation x with y","ρ",0]
["Risk-free interest rate","r",0.05]
["Operating cost for x","cx",0]
["Operating cost for y","cy",0]
["Switching cost from x to y","S12",0.2]
[" "," "," "]
["PV of revenues x","X",{"$f":"(C3/C5-C11/C10)","v":20}," "]
["PV of revenues y","Y",{"$f":"(C4/C6-C12/C10)","v":20}," "]
["Switching boundary x to y","x12",{"$f":"C3","v":1}]
[" "," "," "]
[null,"SOLUTION",null,"OPTION VALUE"]
["Asset value in operating mode '1'","V1(x,y)",{"$f":"C3/C5-C11/C10+C22*C3^C24*C4^C25","v":23.167114578904759},{"$f":"C20-C15","v":3.1671145789047586}]
["Asset value in operating mode '2'","V2(x,y)",{"$f":"C4/C6-C12/C10","v":20},{"$f":"C21-C16","v":0}]
[null,"A",3.1671145789047577]
["Switching boundary x to y","y12 (x)",1.5710542808123311]
["Solution quadrant","β11",-1.7823587246678378,"must be negative",null," "," "]
["Solution quadrant","β12",2.8001823093614622,"must be positive"]
[null,"EQUATIONS",null,"REVISED EQS"]
["Value matching 1","EQ 19",{"$f":"(C22*C23^C25+C17/C5-C11/C10-C23/C6+C12/C10+C13)","v":1.7864827950297979E-9},"(C22*C23^C25+C17/C5-C11/C10-C23/C6+C12/C10+C13)"]
["Smooth pasting 1A","EQ 20",{"$f":"(C24*C22*C23^C25+1/C5)","v":1.484538998397511E-7},"(C24*C22*C23^C25+1/C5)"]
["Smooth pasting 1B","EQ 21",{"$f":"(C25*C22*C23^(C25-1)-1/C6)","v":-1.1253588994009078E-7},"(C25*C22*C23^(C25-1)-1/C6)"]
["Solution quadrant 1","EQ 22",{"$f":"0.5*C7^2*C24*(C24-1)+0.5*C8^2*C25*(C25-1)-C10","v":2.183414560263941E-11},"0.5*C7^2*C24*(C24-1)+0.5*C8^2*C25*(C25-1)-C10"]
[null,"Sum",{"$f":"SUM(ABS(C27:C30))","v":2.6279810672047432E-7}," "]
[null,"SOLVER: SET C31=0, CHANGING C22:C25"]
[{"$f":"B27","v":"EQ 19"},"(C22*C17^C24*C23^C25+C17/C5-C11/C10-C23/C6+C12/C10+C13)"]
[{"$f":"B28","v":"EQ 20"},"(C24*C22*C17^(C24-1)*C23^C25+1/C5)"]
[{"$f":"B29","v":"EQ 21"},"(C25*C22*C17^C24*C23^(C25-1)-1/C6)"]
[{"$f":"B30","v":"EQ 22"},"0.5*C7^2*C24*(C24-1)+0.5*C8^2*C25*(C25-1)+C9*C7*C8*C24*C25+C24*(C10-C5)+C25*(C10-C6)-C10"]
["SPREAD",null,{"$f":"C23-C4","v":0.57105428081233112}]
["V1(x,y)  EQ 5","C3/C5-C11/C10+C22*C3^C24*C4^C25"]
["V2(x,y)  EQ 7","C4/C6-C12/C10"]
["PDE "," ",{"$f":"0.5*(C7^2)*(C3^2)*C43+0.5*(C8^2)*(C4^2)*C44+C9*C7*C8*C3*C4*C45+(C10-C5)*C3*C41+(C10-C6)*C4*C42-C10*C20+(C3-C11)","v":6.9151129267197575E-11}]
["DROV1,x"," ",{"$f":"C24*C22*(C3^(C24-1))*(C4^C25)+1/C5","v":14.355065698266401},"C24*C22*(C3^(C24-1))*(C4^C25)+1/C5"]
["DROV1,y"," ",{"$f":"C25*C22*(C3^C24)*(C4^(C25-1))","v":8.8684982155698791},"C25*C22*(C3^C24)*(C4^(C25-1))"]
["GROV1, x"," ",{"$f":"C24*(C24-1)*C22*(C3^(C24-2))*(C4^C25)","v":15.706232204605232},"C24*(C24-1)*C22*(C3^(C24-2))*(C4^C25)"]
["GROV1,y"," ",{"$f":"C25*(C25-1)*C22*(C3^C24)*(C4^(C25-2))","v":15.96491359827259},"C25*(C25-1)*C22*(C3^C24)*(C4^(C25-2))"]
["GROV1,x,y"," ",{"$f":"C24*C25*C22*(C3^(C24-1))*(C4^(C25-1))","v":-15.806845169222127},"C24*C25*C22*(C3^(C24-1))*(C4^(C25-1))"]
["0.5*(C7^2)*(C3^2)*C43+0.5*(C8^2)*(C4^2)*C44+C9*C7*C8*C3*C4*C45+(C10-C5)*C3*C41+(C10-C6)*C4*C42-C10*C20+(C3-C11)"]
[null,"CALCULATOR ANSWER"]
[null,"y12 (x)",{"$f":"-C25/C24","v":1.5710542836337882},{"$f":"(C23^C25)/(C23^(C25-1))","v":1.5710542808123311}]
[null,null,"-C25/C24","(C23^C25)/(C23^(C25-1))"]
[null,"A",{"$f":"(-1/C5)/(C24*(C17^(C24-1))*C48^C25)","v":3.1671145864863437}]
[null,null,"(-1/C5)/(C24*(C17^(C24-1))*C48^C25)"]
[null,"V1(x,y)",{"$f":"C3/C5-C11/C10+C22*C3^C24*C4^C25","v":23.167114578904759}]
[null,null,"C3/C5-C11/C10+C22*C3^C24*C4^C25"]
[]
["INTERPRETATION","Switch from RO (x) to IA (y) if y=1.57 while x=1."]
[null,"The current value of taking RO is 23.17, since there is an embedded option to"]
[null,"switch once to IA."]
[null,"Switching now would involve giving up 23.17 for IA worth 20,"]
[null,"since there is no option to switch back."]

{"sheet":"Exercise 12.2","index":32,"n_rows":50,"n_cols":8,"rows_kept":50,"cut":false,"hidden":false,"merged":[]}
["SINGLE American Perpetual IN-OUT SWITCH Option"]
["Exercise 12.2","INPUT",null,null," "]
["PRICE Yarn","x",10," "]
["COST Cotton","y",9," "]
["Convenience yield of x","δx",0.04]
["Convenience yield of y","δy",0.04]
["Volatility of x","σx",0.4]
["Volatility of y","σy",0.4]
["Correlation x with y","ρ",0.5]
["Risk-free interest rate","r",0.05]
["Switching cost from x to y","S12",10]
["Switching boundary OP to SHUT","x12",{"$f":"C3","v":10}]
[null,"SOLUTION",null,"OPTION","OPERATING"]
["Asset value in operating mode ","V1(x,y)",{"$f":"(C15*C3^C17*C4^C18+C3/C5-C4/C6)","v":121.20774602925491},{"$f":"(C15*C3^C17*C4^C18)","v":96.20774602925492},{"$f":"C3/C5-C4/C6","v":25}," "]
[null,"A",10.756021233920901]
["Switching boundary OP to SHUT","y12 (x)",38.669161597444685,{"$f":"-C18*C12/C17","v":38.669161302708375}]
["Solution quadrant","β11",-0.35374236680289733,"must be negative"]
["Solution quadrant","β12",1.367892064150307,"must be positive"]
[null,"EQUATIONS"]
["Value matching 1","EQ 35",{"$f":"(C15*C12^C17*C16^C18+C12/C5-C16/C6+C11)","v":-4.6961872612882871E-7}," "]
["Smooth pasting 1A","EQ 36",{"$f":"(C17*C15*(C12^(C17-1))*C16^C18+1/C5)","v":-3.1092173102820198E-7}," "]
["Smooth pasting 1B","EQ 37",{"$f":"(C18*C15*C12^C17*(C16^(C18-1))-1/C6)","v":1.2037175878276685E-7}," "]
["Solution quadrant 1","EQ 38",{"$f":"0.5*C7^2*C17*(C17-1)+0.5*C8^2*C18*(C18-1)+C9*C7*C8*C17*C18+C17*(C10-C5)+C18*(C10-C6)-C10","v":-1.9656852534577496E-11}]
[null,"Sum",{"$f":"SUM(ABS(C20:C23))","v":9.0093187279233211E-7}," "]
[null,"SOLVER: SET C24=0, CHANGING C15:C18"]
["SPREAD",null,{"$f":"C16-C4","v":29.669161597444685}]
["V1(x,y)","(C15*C3^C17*C4^C18+C3/C5-C4/C6)"]
[{"$f":"B20","v":"EQ 35"},"(C15*C12^C17*C16^C18+C12/C5-C16/C6+C11)"]
[{"$f":"B21","v":"EQ 36"},"(C17*C15*(C12^(C17-1))*C16^C18+1/C5)"]
[{"$f":"B22","v":"EQ 37"},"(C18*C15*C12^C17*(C16^(C18-1))-1/C6)"]
[{"$f":"B23","v":"EQ 38"},"0.5*C7^2*C17*(C17-1)+0.5*C8^2*C18*(C18-1)+C9*C7*C8*C17*C18+C17*(C10-C5)+C18*(C10-C6)-C10"]
["PDE "," ",{"$f":"0.5*(C7^2)*(C3^2)*C35+0.5*(C8^2)*(C4^2)*C36+C9*C7*C8*C3*C4*C37+(C10-C5)*C3*C33+(C10-C6)*C4*C34-C10*C14+(C3-C4)","v":-1.8911414656486158E-9}]
["DROV1,x"," ",{"$f":"C15*C17*(C3^(C17-1))*(C4^C18)+1/C5","v":21.596724421483934},"C15*C17*(C3^(C17-1))*(C4^C18)+1/C5",null,null,null," "]
["DROV1,y"," ",{"$f":"C15*C18*(C3^(C17))*(C4^(C18-1))-1/C6","v":-10.377576410754891},"C15*C18*(C3^(C17))*(C4^(C18-1))-1/C6",null,null,null," "]
["GROV1, x"," ",{"$f":"C15*C17*(C17-1)*(C3^(C17-2))*(C4^C18)","v":0.46071583365428398},"C15*C17*(C17-1)*(C3^(C17-2))*(C4^C18)",null,null,null," "]
["GROV1,y"," ",{"$f":"C15*C18*(C18-1)*(C3^(C17))*(C4^(C18-2))","v":0.59771928856972467},"C15*C18*(C18-1)*(C3^(C17))*(C4^(C18-2))",null,null,null," "]
["GROV1,x,y"," ",{"$f":"C15*C17*C18*(C3^(C17-1))*(C4^(C18-1))","v":-0.51725707288540812},"C15*C17*C18*(C3^(C17-1))*(C4^(C18-1))",null,null,null," "]
["0.5*(C7^2)*(C3^2)*C35+0.5*(C8^2)*(C4^2)*C36+C9*C7*C8*C3*C4*C37+(C10-C5)*C3*C33+(C10-C6)*C4*C34-C10*C14+(C3-C4)"]
[null,"CALCULATOR ANSWER"]
[null,"y12 (x)",{"$f":"-C18*C12/C17","v":38.669161302708375}," ",null,null," "," "]
[null,null,"-C18*C12/C17",null,null,null," "," "]
[null,"A",{"$f":"(-1/C5)/(C17*(C12^(C17-1))*C16^C18)","v":10.756021100149674}]
[null,null,"(-1/C5)/(C17*(C12^(C17-1))*C16^C18)"]
[null,"V1(x,y)",{"$f":"C3/C5-C4/C6+C42*C3^C17*C4^C18","v":121.20774483273178}]
[null,null,"C3/C5-C4/C6+C42*C3^C17*C4^C18"]
["INTERPRETATION","Switch from active when yarn=10, cotton=9 to permanent shutdown if y=38.67 while x=10."]
[null,"The current value of the mill with its embedded shut-down option is 121.21"]
[null,"with a small current operating value of 25, and a large abandonment option value."]
[null," "]
[null,"since there is no option to switch back."]

{"sheet":"Exercise 12.3","index":33,"n_rows":54,"n_cols":4,"rows_kept":54,"cut":false,"hidden":false,"merged":[]}
["Continuous American Perpetual SINGLE SWITCH Option"]
["ONE WAY SWITCH FROM INPUT x TO y ",null," ","Exercise 12.3"]
["INPUT x canola","x",1," "]
["INPUT y palm oil","y",1," "]
["Convenience yield of x","δx",0.04]
["Convenience yield of y","δy",0.04]
["Volatility of x","σx",0.2]
["Volatility of y","σy",0.2]
["Correlation x with y","ρ",0.5]
["Risk-free interest rate","r",0.05]
["Output price for x","px",3]
["Output price for y","py",3]
["Switching cost from x to y","S12",1]
[" "," "," "]
["PV of revenues using x","X",{"$f":"(-C3/C5+C11/C10)","v":35}," "]
["PV of revenues using y","Y",{"$f":"(-C4/C6+C12/C10)","v":35}," "]
["Switching boundary x to y","x12",{"$f":"C3","v":1}]
[" "," "," "]
[null,"SOLUTION",null,"OPTION VALUE"]
["Asset value in operating mode '1'","V1(x,y)",{"$f":"-C3/C5+C11/C10+C22*C3^C24*C4^C25","v":41.066435190951765},{"$f":"C20-C15","v":6.0664351909517649}]
["Asset value in operating mode '2'","V2(x,y)",{"$f":"-C4/C6+C12/C10","v":35}," "]
[null,"A",6.0664351909517675," "]
["Switching boundary x to y","y12 (x)",0.46286271680021174]
["Solution quadrant","β11",2.0115168096068259,"must be positive"]
["Solution quadrant","β12",-0.93105613550377053,"must be negative"]
[null,"EQUATIONS"]
["Value matching 1","EQ 53",{"$f":"C22*C17^C24*C23^C25-C17/C5+C11/C10+C23/C6-C12/C10+C13","v":1.9731388078980672E-8}]
["Smooth pasting 1A","EQ 54",{"$f":"C24*C22*C17^(C24-1)*C23^C25-1/C5","v":8.5656100878850339E-8}]
["Smooth pasting 1B","EQ 55",{"$f":"C25*C22*C17^C24*C23^(C25-1)+1/C6","v":-8.8874518411330428E-8}]
["Solution quadrant 1","EQ 56",{"$f":"0.5*C7^2*C24*(C24-1)+0.5*C8^2*C25*(C25-1)+C9*C7*C8*C24*C25+C24*(C10-C5)+C25*(C10-C6)-C10","v":-3.0297854503036348E-11}]
[null,"Sum",{"$f":"SUM(ABS(C27:C30))","v":1.9429230522366447E-7}," "]
[null,"SOLVER: SET C31=0, CHANGING C22:C25"]
["EQ 53","C22*C17^C24*C23^C25-C17/C5+C11/C10+C23/C6-C12/C10+C13"]
["EQ 54","C24*C22*C17^(C24-1)*C23^C25-1/C5"]
["EQ 55","C25*C22*C17^C24*C23^(C25-1)+1/C6"]
["EQ 56","0.5*C7^2*C24*(C24-1)+0.5*C8^2*C25*(C25-1)+C9*C7*C8*C24*C25+C24*(C10-C5)+C25*(C10-C6)-C10"]
["SPREAD",null,{"$f":"-C23+C4","v":0.53713728319978826}]
["V1(x,y)  ","-C3/C5+C11/C10+C22*C3^C24*C4^C25"]
["V2(x,y)  ","-C4/C6+C12/C10"]
["PDE "," ",{"$f":"0.5*(C7^2)*(C3^2)*C43+0.5*(C8^2)*(C4^2)*C44+C9*C7*C8*C3*C4*C45+(C10-C5)*C3*C41+(C10-C6)*C4*C42-C10*C20+(C11-C3)","v":-1.8380008626195377E-10}]
["DROV1,x"," ",{"$f":"C24*C22*(C3^(C24-1))*(C4^C25)-1/C5","v":-12.797263639010126},"C24*C22*(C3^(C24-1))*(C4^C25)-1/C5"]
["DROV1,y"," ",{"$f":"C25*C22*(C3^C24)*(C4^(C25-1))","v":-5.648191705171631},"C25*C22*(C3^C24)*(C4^(C25-1))"]
["GROV1, x"," ",{"$f":"C24*(C24-1)*C22*(C3^(C24-2))*(C4^C25)","v":12.343272952341687},"C24*(C24-1)*C22*(C3^(C24-2))*(C4^C25)"]
["GROV1,y"," ",{"$f":"C25*(C25-1)*C22*(C3^C24)*(C4^(C25-2))","v":10.906975246773181},"C25*(C25-1)*C22*(C3^C24)*(C4^(C25-2))"]
["GROV1,x,y"," ",{"$f":"C24*C25*C22*(C3^(C24-1))*(C4^(C25-1))","v":-11.361432558834577},"C24*C25*C22*(C3^(C24-1))*(C4^(C25-1))"]
["0.5*(C7^2)*(C3^2)*C43+0.5*(C8^2)*(C4^2)*C44+C9*C7*C8*C3*C4*C45+(C10-C5)*C3*C41+(C10-C6)*C4*C42-C10*C20+(C11-C3)"]
[null,"CALCULATOR ANSWER"]
[null,"y12 (x)",{"$f":"-C25/C24","v":0.46286271685979902},"-C25/C24"]
[null,"V1(x,y)",{"$f":"-C3/C5+C11/C10+6.07*C3^C24*C4^C25","v":41.07},"-C3/C5+C11/C10+6.07*C3^C24*C4^C25"]
["INTERPRETATION","Switch from canola (x) to palm oil (y) if y=.46 while x is still 1."]
[null,"The current value of this flexible factility is 41.07 which is the current "]
[null,"operating value of 35 plus the switch option value of 6.07."]
[null,"Switching now would involve giving up the option value for the same operating value."]
[null,"since there is no option to switch back."]

{"sheet":"Problem 12.4","index":34,"n_rows":54,"n_cols":4,"rows_kept":54,"cut":false,"hidden":false,"merged":[]}
["Continuous American Perpetual SINGLE SWITCH Option"]
["ONE WAY SWITCH FROM OUTPUT x TO y ",null,"PROBLEM 12.4"]
["OUTPUT x Edward","x",7000," "]
["OUTPUT y Robert","y",10000," "]
["Convenience yield of x","δx",0.04]
["Convenience yield of y","δy",0.04]
["Volatility of x","σx",0.2]
["Volatility of y","σy",0.2]
["Correlation x with y","ρ",0]
["Risk-free interest rate","r",0.05]
["Operating cost for x","cx",0]
["Operating cost for y","cy",0]
["Switching cost from x to y","S12",3000]
[" "," "," "]
["PV of revenues x","X",{"$f":"(C3/C5-C11/C10)","v":175000},"LHS EQ5"]
["PV of revenues y","Y",{"$f":"(C4/C6-C12/C10)","v":250000},"LHS EQ 7"]
["Switching boundary x to y","x12",{"$f":"C3","v":7000}]
[" "," "," "]
[null,"SOLUTION",null,"OPTION VALUE"]
["Asset value in operating mode '1'","V1(x,y)",{"$f":"C3/C5-C11/C10+C22*C3^C24*C4^C25","v":279883.72162091616},{"$f":"C20-C15","v":104883.72162091616}]
["Asset value in operating mode '2'","V2(x,y)",{"$f":"C4/C6-C12/C10","v":250000},{"$f":"C21-C16","v":0}]
[null,"A",7.6565004238263192]
["Switching boundary x to y","y12 (x)",18565.267400536639]
["Solution quadrant","β11",-0.61160650555624085,"must be negative"]
["Solution quadrant","β12",1.6220911885086871,"must be positive"]
[null,"EQUATIONS",null," "]
["Value matching 1","EQ 19",{"$f":"(C22*C17^C24*C23^C25+C17/C5-C11/C10-C23/C6+C12/C10+C13)","v":1.3440148904919624E-7}," "]
["Smooth pasting 1A","EQ 20",{"$f":"(C24*C22*C17^(C24-1)*C23^C25+1/C5)","v":-8.0397910551255336E-12}," "]
["Smooth pasting 1B","EQ 21",{"$f":"(C25*C22*C17^C24*C23^(C25-1)-1/C6)","v":1.1162626378791174E-11}," "]
["Solution quadrant 1","EQ 22",{"$f":"0.5*C7^2*C24*(C24-1)+0.5*C8^2*C25*(C25-1)+C9*C7*C8*C24*C25+C24*(C10-C5)+C25*(C10-C6)-C10","v":3.6082248300317588E-16}," "]
[null,"Sum",{"$f":"SUM(ABS(C27:C30))","v":1.3442069182745264E-7}," "]
[null,"SOLVER: SET C31=0, CHANGING C22:C25"]
[{"$f":"B27","v":"EQ 19"},"(C22*C17^C24*C23^C25+C17/C5-C11/C10-C23/C6+C12/C10+C13)"]
[{"$f":"B28","v":"EQ 20"},"(C24*C22*C17^(C24-1)*C23^C25+1/C5)"]
[{"$f":"B29","v":"EQ 21"},"(C25*C22*C17^C24*C23^(C25-1)-1/C6)"]
[{"$f":"B30","v":"EQ 22"},"0.5*C7^2*C24*(C24-1)+0.5*C8^2*C25*(C25-1)+C9*C7*C8*C24*C25+C24*(C10-C5)+C25*(C10-C6)-C10"]
["SPREAD",null,{"$f":"C23-C4","v":8565.2674005366389}]
["V1(x,y)  EQ 5","C3/C5-C11/C10+C22*C3^C24*C4^C25"]
["V2(x,y)  EQ 7","C4/C6-C12/C10"]
["PDE "," ",{"$f":"0.5*(C7^2)*(C3^2)*C43+0.5*(C8^2)*(C4^2)*C44+C9*C7*C8*C3*C4*C45+(C10-C5)*C3*C41+(C10-C6)*C4*C42-C10*C20+(C3-C11)","v":3.092281986027956E-11}]
["DROV1,x"," ",{"$f":"C24*C22*(C3^(C24-1))*(C4^C25)+1/C5","v":15.836061932814005},"C24*C22*(C3^(C24-1))*(C4^C25)+1/C5"]
["DROV1,y"," ",{"$f":"C25*C22*(C3^C24)*(C4^(C25-1))","v":17.013096065928586},"C25*C22*(C3^C24)*(C4^(C25-1))"]
["GROV1, x"," ",{"$f":"C24*(C24-1)*C22*(C3^(C24-2))*(C4^C25)","v":2.1098088865130656E-3},"C24*(C24-1)*C22*(C3^(C24-2))*(C4^C25)"]
["GROV1,y"," ",{"$f":"C25*(C25-1)*C22*(C3^C24)*(C4^(C25-2))","v":1.0583697151865974E-3},"C25*(C25-1)*C22*(C3^C24)*(C4^(C25-2))"]
["GROV1,x,y"," ",{"$f":"C24*C25*C22*(C3^(C24-1))*(C4^(C25-1))","v":-1.4864743190821704E-3},"C24*C25*C22*(C3^(C24-1))*(C4^(C25-1))"]
["0.5*(C7^2)*(C3^2)*C43+0.5*(C8^2)*(C4^2)*C44+C9*C7*C8*C3*C4*C45+(C10-C5)*C3*C41+(C10-C6)*C4*C42-C10*C20+(C3-C11)"]
["INTERPRETATION","Lucy should switch from Edward to Robert once, when Robert's annual income"]
[null,"is 18,565 or 85% more than currently. "]
[null,"Switching now would involve giving up a value of 279,883 for 250,000,"]
[null,"since there is no option to switch back."]
[null,"This assumes Lucy gets all of Robert's income, and both live forever."]
[null,"Considering Lucy's wile, the first assumption might not be unrealistic,"]
[null,"and since these are fictional characters, immortality is a possibility, "]
[null,"so since Jane Austin is still polular after almost 200 years."]

{"sheet":"Problem 12.5","index":35,"n_rows":62,"n_cols":5,"rows_kept":62,"cut":false,"hidden":false,"merged":[]}
["Continuous American Perpetual SINGLE SWITCH Option"]
["ONE WAY SWITCH FROM OUTPUT x TO y",null," ","PROBLEM 12.5"]
["OUTPUT x Willoughby","x",1," "]
["OUTPUT y Colonel Brandon","y",1," "]
["Convenience yield of x","δx",0.05]
["Convenience yield of y","δy",0.05]
["Volatility of x","σx",0.1]
["Volatility of y","σy",0.1]
["Correlation x with y","ρ",0]
["Risk-free interest rate","r",0.05]
["Operating cost for x","cx",0]
["Operating cost for y","cy",0]
["Switching cost from x to y","S12",1]
["Switching cost from y to x","S21"," "]
["PV of revenues x","X",{"$f":"(C3/C5-C11/C10)","v":20}," "]
["PV of revenues y","Y",{"$f":"(C4/C6-C12/C10)","v":20}," "]
["Switching boundary x to y","x12",{"$f":"C3","v":1}]
[" "," "," "]
[null,"SOLUTION",null,"OPTION VALUE"]
["Asset value in operating mode '1'","V1(x,y)",{"$f":"C3/C5-C11/C10+C22*C3^C24*C4^C25","v":22.904197647734094},{"$f":"C20-C15","v":2.9041976477340938}]
["Asset value in operating mode '2'","V2(x,y)",{"$f":"C4/C6-C12/C10","v":20},{"$f":"C21-C16","v":0}]
[null,"A",2.9041976477340947]
["Switching boundary x to y","y12 (x)",1.6223471214340481,null,{"$f":"-(C25/C24)","v":1.6223470762739016}]
["Solution quadrant","β11",-1.7471916537030259,"must be negative"]
["Solution quadrant","β12",2.8345512710752674,"must be positive"]
[null,"EQUATIONS"]
["Value matching 1","EQ 19",{"$f":"(C22*C17^C24*C23^C25+C17/C5-C11/C10-C23/C6+C12/C10+C13)","v":-8.7639183021792633E-8}]
["Smooth pasting 1A","EQ 20",{"$f":"(C24*C22*C17^(C24-1)*C23^C25+1/C5)","v":-2.1186879735068942E-6}]
["Smooth pasting 1B","EQ 21",{"$f":"(C25*C22*C17^C24*C23^(C25-1)-1/C6)","v":1.5619618487505704E-6}]
["Solution quadrant 1","EQ 22",{"$f":"0.5*C7^2*C24*(C24-1)+0.5*C8^2*C25*(C25-1)+C9*C7*C8*C24*C25+C24*(C10-C5)+C25*(C10-C6)-C10","v":-1.7124156259651357E-10}]
[null,"Sum",{"$f":"SUM(ABS(C27:C30))","v":3.7684602468418538E-6}," "]
[null,"SOLVER: SET C31=0, CHANGING C22:C25"]
[{"$f":"B27","v":"EQ 19"},"(C22*C17^C24*C23^C25+C17/C5-C11/C10-C23/C6+C12/C10+C13)"]
[{"$f":"B28","v":"EQ 20"},"(C24*C22*C17^(C24-1)*C23^C25+1/C5)"]
[{"$f":"B29","v":"EQ 21"},"(C25*C22*C17^C24*C23^(C25-1)-1/C6)"]
[{"$f":"B30","v":"EQ 22"},"0.5*C7^2*C24*(C24-1)+0.5*C8^2*C25*(C25-1)+C9*C7*C8*C24*C25+C24*(C10-C5)+C25*(C10-C6)-C10"]
["SPREAD",null,{"$f":"C23-C4","v":0.62234712143404813}]
["V1(x,y)  EQ 5","C3/C5-C11/C10+C22*C3^C24*C4^C25"]
["V2(x,y)  EQ 7","C4/C6-C12/C10"]
["PDE "," ",{"$f":"0.5*(C7^2)*(C3^2)*C43+0.5*(C8^2)*(C4^2)*C44+C9*C7*C8*C3*C4*C45+(C10-C5)*C3*C41+(C10-C6)*C4*C42-C10*C20+(C3-C11)","v":-4.973192968549256E-10}]
["DROV1,x"," ",{"$f":"C24*C22*(C3^(C24-1))*(C4^C25)+1/C5","v":14.925810109175028},"C24*C22*(C3^(C24-1))*(C4^C25)+1/C5"]
["DROV1,y"," ",{"$f":"C25*C22*(C3^C24)*(C4^(C25-1))","v":8.2320971338384794},"C25*C22*(C3^C24)*(C4^(C25-1))"]
["GROV1, x"," ",{"$f":"C24*(C24-1)*C22*(C3^(C24-2))*(C4^C25)","v":13.939772117378627},"C24*(C24-1)*C22*(C3^(C24-2))*(C4^C25)"]
["GROV1,y"," ",{"$f":"C25*(C25-1)*C22*(C3^C24)*(C4^(C25-2))","v":15.102204260498448},"C25*(C25-1)*C22*(C3^C24)*(C4^(C25-2))"]
["GROV1,x,y"," ",{"$f":"C24*C25*C22*(C3^(C24-1))*(C4^(C25-1))","v":-14.383051404715193},"C24*C25*C22*(C3^(C24-1))*(C4^(C25-1))"]
["0.5*(C7^2)*(C3^2)*C43+0.5*(C8^2)*(C4^2)*C44+C9*C7*C8*C3*C4*C45+(C10-C5)*C3*C41+(C10-C6)*C4*C42-C10*C20+(C3-C11)"]
["INTERPRETATION","Marianne should switch from Willoughby to Colonel Brandon once, "]
[null,"when Brandon's annual income is greater than 1.62. "]
[null,"Switching now would involve giving up a value of 22.90 for 20,"]
[null,"since there is no option to switch back."]
[null,"This assumes Marianne gets all of Robert's income, and both live forever."]
[null,"Considering Marianne's beauty, the first assumption might not be unrealistic,"]
[null,"and since these are fictional characters, immortality is a possibility, "]
[null,"since Jane Austin is still polular after almost 200 years."]
[null,"Marianne may not be solely interested in money."]
[null,"CALCULATOR ANSWER"]
[null,"y12 (x)",{"$f":"-C25*C17/C24","v":1.6223470762739016}]
[null,null,"-C25*C17/C24"]
[null,"A",{"$f":"(-1/C5)/(C24*(C17^(C24-1))*(C57^C25))","v":2.9041975692308362}]
[null,"(-1/C25)/(C24*(C17^(C24-1))*C56^C25)"]
[null,"V1(x,y)",{"$f":"C59*C3^C24*C4^C25","v":2.9041975692308362}]
[null,"C44*C3^C24*C4^C25"]

{"sheet":"Problem 12.6","index":36,"n_rows":80,"n_cols":7,"rows_kept":80,"cut":false,"hidden":false,"merged":[]}
["                         Multiple American Perpetual Input Switching Option"]
["Problem 12.6"," "," "]
["INPUT x canola","x",1," "]
["INPUT y palm oil","y",1," "]
["Convenience yield of x","δx",0.04]
["Convenience yield of y","δy",0.04]
["Volatility of x","σx",0.2]
["Volatility of y","σy",0.2]
["Correlation x with y","ρ",0.5]
["Risk-free interest rate","r",0.05]
["Output price for x","px",3]
["Output price for y","py",3]
[" "," "," "]
[" "," "," "]
["Switching cost from x to y","S12",0.5]
["Switching cost from y to x","S21",0.5]
["PV of revenues using x","X",{"$f":"(-C3/C5+C11/C10)","v":35}," "]
["PV of revenues using y","Y",{"$f":"(-C4/C6+C12/C10)","v":35}," "]
["Switching boundary x to y","x12",{"$f":"C3","v":1}]
["Switching boundary y to x","x21",1]
[null,"SOLUTION",null,"OPTION VALUE"]
["Asset value in operating mode '1'","V1(x,y)",{"$f":"-C3/C5+C11/C10+C24*C3^C28*C4^C29","v":42.691763448752361},{"$f":"C22-C17","v":7.6917634487523614}]
["Asset value in operating mode '2'","V2(x,y)",{"$f":"-C4/C6+C12/C10+C25*C3^C30*C4^C31","v":42.687994293305998},{"$f":"C23-C18","v":7.6879942933059979}]
[null,"A",7.6917634487523596]
[null,"B",7.6879942933059979]
["Switching boundary x to y","y12 (x)",0.70927643330695334]
["Switching boundary y to x","y21 (x)",1.3545989456187268]
["Solution quadrant","β11",2.0102777797852318,"must be positive",null," "," "]
["Solution quadrant","β12",-0.93995469994225744,"must be negative",null," "," "]
["Solution quadrant","β21",-0.94539927506188204,"must be negative",null," "," "]
["Solution quadrant","β22",2.0094845231460576,"must be positive",null," "," "]
[null,"EQUATIONS"]
["Value matching 1","EQ47",{"$f":"(C24*C19^C28*C26^C29-C19/C5+C11/C10-C25*C19^C30*C26^C31+C26/C6-C12/C10+C15)","v":-1.3740724114086333E-8}]
["Value matching 2","EQ48",{"$f":"(C24*C20^C28*C27^C29-C20/C5+C11/C10-C25*C20^C30*C27^C31+C27/C6-C12/C10-C16)","v":1.2165671137154277E-8}]
["Smooth pasting 1A","EQ49",{"$f":"(C28*C24*C19^(C28-1)*C26^C29-1/C5-C30*C25*C19^(C30-1)*C26^C31)","v":-1.3872460957742305E-7}]
["Smooth pasting 1B","EQ50",{"$f":"(C29*C24*C19^C28*C26^(C29-1)-C31*C25*C19^C30*C26^(C31-1)+1/C6)","v":1.4856354724201992E-7}]
["Smooth pasting 2A","EQ51",{"$f":"(C28*C24*C20^(C28-1)*C27^C29-1/C5-C30*C25*C20^(C30-1)*C27^C31)","v":-1.6892607312968266E-7}]
["Smooth pasting 2B","EQ52",{"$f":"(C29*C24*C20^C28*C27^(C29-1)-C31*C25*C20^C30*C27^(C31-1)+1/C6)","v":1.5437609235391392E-7}]
["Solution quadrant 1","EQ44",{"$f":"0.5*C7^2*C28*(C28-1)+0.5*C8^2*C29*(C29-1)+C9*C7*C8*C28*C29+C28*(C10-C5)+C29*(C10-C6)-C10","v":5.2433564434739566E-11}]
["Solution quadrant 2","EQ46",{"$f":"0.5*C7^2*C30*(C30-1)+0.5*C8^2*C31*(C31-1)+C9*C7*C8*C30*C31+C30*(C10-C5)+C31*(C10-C6)-C10","v":5.1572579540248853E-11}]
[null,"Sum",{"$f":"SUM(ABS(C33)+ABS(C34)+ABS(C35)+ABS(C36)+ABS(C37)+ABS(C38)+ABS(C39)+ABS(C40))","v":6.3660072369825516E-7}," "]
[null,"SOLVER: SET C41=0, CHANGING C24:C31"]
[" ","Spread",{"$f":"C27-C26","v":0.64532251231177351}]
["PDE","EQ 35",{"$f":"0.5*(C7^2)*(C3^2)*C50+0.5*(C8^2)*(C4^2)*C51+C9*C7*C8*C3*C4*C52+(C10-C5)*C3*C48+(C10-C6)*C4*C49-C10*C46+(-C3+C11)","v":4.0330627726348212E-10}]
[null,"EQ 36",{"$f":"0.5*(C7^2)*(C3^2)*C57+0.5*(C8^2)*(C4^2)*C58+C9*C7*C8*C3*C4*C59+(C10-C5)*C3*C55+(C10-C6)*C4*C56-C10*C53+(-C4+C12)","v":3.9648950789228365E-10}]
["Switch x to y","V1(x,y)",{"$f":"IF(C4>C26,-C3/C5+C11/C10+C24*C3^C28*C4^C29,C47)","v":42.691763448752361}]
[null,"V*-K",{"$f":"C12/C10-C26/C6-C15","v":41.768089167326167}," "]
[null,"F'(x)",{"$f":"-1/C5+C28*C24*C3^(C28-1)*C4^C29","v":-9.5374188516089085}]
[null,"F'(y)",{"$f":"C29*C24*C3^C28*C4^(C29-1)","v":-7.2299092044988473}]
[null,"F''(x)",{"$f":"C28*(C28-1)*C24*C3^(C28-2)*C4^C29","v":15.621502152345531}]
[null,"F''(y)",{"$f":"C29*(C29-1)*C24*C3^C28*C4^(C29-2)","v":14.025696341423327}]
[null,"F''(x,y)",{"$f":"C28*C29*C24*C3^(C28-1)*C4^(C29-1)","v":-14.534125823668754}]
["Switch y to x","V2(x,y)",{"$f":"-C4/C6+C12/C10+C25*C3^C30*C4^C31","v":42.687994293305998}]
[null,"V*-K",{"$f":"C11/C10-C20/C5-C16","v":34.5}," "]
[null,"F'(x)",{"$f":"C30*C25*C3^(C30-1)*C4^C31","v":-7.2682242315713763}]
[null,"F'(y)",{"$f":"-1/C6+C31*C25*C3^C30*C4^(C31-1)","v":-9.5510944535663835}]
[null,"F''(x)",{"$f":"C30*(C30-1)*C25*C3^(C30-2)*C4^C31","v":14.13959815108616}]
[null,"F''(y)",{"$f":"C31*(C31-1)*C25*C3^C30*C4^(C31-2)","v":15.595431048670024}]
[null,"F''(x,y)",{"$f":"C30*C31*C25*C3^(C30-1)*C4^(C31-1)","v":-14.60538410409783}]
["EQ 35","0.5*(C7^2)*(C3^2)*C50+0.5*(C8^2)*(C4^2)*C51+C9*C7*C8*C3*C4*C52+(C10-C5)*C3*C48+(C10-C6)*C4*C49-C10*C46+(-C3+C11)"]
["EQ 36","0.5*(C7^2)*(C3^2)*C57+0.5*(C8^2)*(C4^2)*C58+C9*C7*C8*C3*C4*C59+(C10-C5)*C3*C55+(C10-C6)*C4*C56-C10*C53+(-C4+C12)"]
["Switch x to y","V1(x,y)","IF(C4>C26,-C3/C5+C11/C10+C24*C3^C28*C4^C29,C47)"]
[null,"V*-K","C12/C10-C26/C6-C15"]
[null,"F'(x)","-1/C5+C28*C24*C3^(C28-1)*C4^C29"]
[null,"F'(y)","C29*C24*C3^C28*C4^(C29-1)"]
[null,"F''(x)","C28*(C28-1)*C24*C3^(C28-2)*C4^C29"]
[null,"F''(y)","C29*(C29-1)*C24*C3^C28*C4^(C29-2)"]
[null,"F''(x,y)","C28*C29*C24*C3^(C28-1)*C4^(C29-1)"]
["Switch y to x","V2(x,y)","-C4/C6+C12/C10+C25*C3^C30*C4^C31"]
[null,"V*-K","C11/C10-C20/C5-C16"]
[null,"F'(x)","C30*C25*C3^(C30-1)*C4^C31"]
[null,"F'(y)","-1/C6+C31*C25*C3^C30*C4^(C31-1)"]
[null,"F''(x)","C30*(C30-1)*C25*C3^(C30-2)*C4^C31"]
[null,"F''(y)","C31*(C31-1)*C25*C3^C30*C4^(C31-2)"]
[null,"F''(x,y)","C30*C31*C25*C3^(C30-1)*C4^(C31-1)"]
["INTERPRETATION","George should switch from canola (if x=1) when palm oil y<.71."]
[null,"After that switch, George should switch from to canola (if x=1) when palm oil y>1.35."]
[null,"HBS is worth 42.69 due to this flexibility for multiple input switching,  "]
[null,"35 if no switching allowed, and"]
[null,"from Exercise 12.3  with similar parameter values, 41.07 if only one switch is possible."]

{"sheet":"Tutor1","index":37,"n_rows":1,"n_cols":1,"rows_kept":1,"cut":false,"hidden":false,"merged":[]}
["See Exercise 3.2"]

{"sheet":"Tutor2","index":38,"n_rows":32,"n_cols":3,"rows_kept":32,"cut":false,"hidden":false,"merged":[]}
["                                                       Perpetual American Call Option"]
["INPUT",null,"Tutorial 2"]
["V",8]
["K",7.5]
["s",0.5]
["r",0.1]
["d",0.1]
["OUTPUT"]
["ROV",{"$f":"IF(B3<B13,(B13-B4)*(B3/B13)^B15,B11)","v":3.1012364618020949},"IF(B3<B13,(B13-B4)*(B3/B13)^B15,B11)"]
["ROV",{"$f":"IF(B3<B13,B14*(B3^B15),B11)","v":3.101236461802094},"IF(B3<B13,B14*(B3^B15),B11)"]
["V-K",{"$f":"B3-B4","v":0.5},"B3-B4"]
["F'(V)",{"$f":"IF(B3<B13,B14*B15*(B3^(B15-1)),1)","v":0.59105499558369101},"IF(B3<B13,B14*B15*(B3^(B15-1)),1)"]
["V*",{"$f":"(B15/(B15-1))*B4","v":21.794016343087122},"(B15/(B15-1))*B4"]
["A",{"$f":"(B13-B4)/(B13^B15)","v":0.13019611477972681},"(B13-B4)/(B13^B15)"]
["b1",{"$f":"0.5-(B6-B7)/(B5^2)+SQRT(((B6-B7)/(B5^2)-0.5)^2 + 2*B6/(B5^2))","v":1.5246950765959599},"0.5-(B6-B7)/(B5^2)+SQRT(((B6-B7)/(B5^2)-0.5)^2 + 2*B6/(B5^2))"]
[" "," "," "]
["ODE",{"$f":"0.5*(B5^2)*(B3^2)*B18+(B6-B7)*B3*B12-B6*B10","v":0},"0.5*(B5^2)*(B3^2)*B18+(B6-B7)*B3*B12-B6*B10"]
["F''(V)",{"$f":"IF(B3<B13,B14*B15*(B15-1)*(B3^(B15-2)),0)","v":3.87654557725262E-2},"IF(B3<B13,B14*B15*(B15-1)*(B3^(B15-2)),0)"]
["F'(V*)",{"$f":"B14*B15*(B13^(B15-1))","v":1.0000000000000004},"B14*B15*(B13^(B15-1))"]
["F(V*)",{"$f":"IF(B3<B13,B14*(B13^B15),B11)","v":14.294016343087124},"IF(B3<B13,B14*(B13^B15),B11)"]
["V*-K",{"$f":"B13-B4","v":14.294016343087122},"B13-B4"]
["# Projects",5," "]
["TOTAL ROV",{"$f":"B10*B22","v":15.50618230901047},"B10*B22"]
["ROV-NPV",{"$f":"B22*(B9-B11)","v":13.006182309010475},"B22*(B9-B11)"]
[" "," "]
["SECURITY ANALYSIS OF ISEG-GAS S.A.."]
["ASSETS","LIAB"]
[10,10]
[{"$f":"B24","v":13.006182309010475},{"$f":"A30-B28","v":13.006182309010477},"NET APPRAISED RO VALUE"]
[{"$f":"A28+A29","v":23.006182309010477},null,"Total Assets"]
["SHARES",10]
[null,{"$f":"B29/B31","v":1.3006182309010477},"NET REAL OPTION VALUE/SHARE"]

{"sheet":"Tutor3","index":39,"n_rows":1,"n_cols":1,"rows_kept":1,"cut":false,"hidden":false,"merged":[]}
["See Problem 12.5"]

{"sheet":"Tutor4","index":40,"n_rows":21,"n_cols":4,"rows_kept":21,"cut":false,"hidden":false,"merged":[]}
[null,"Real Debt Strategy Simple"]
[" "]
[" "]
[null,"ACCOUNTS","REAL"]
["ASSETS",{"$f":"B10","v":100},{"$f":"C10-C15*C19*(C19/C10)^-C20","v":95.1171875},"C10-C15*C19*(C19/C10)^-C20"]
["DEBT",{"$f":"B12","v":62.5},{"$f":"B6+((1-C15)*C19-B6)*(C19/C10)^-C20","v":47.8515625},"B6+((1-C15)*C19-B6)*(C19/C10)^-C20"]
["EQ",{"$f":"B5-B6","v":37.5},{"$f":"C5-C6","v":47.265625},"C5-C6"]
[]
["INPUT"," "]
["NOM V",100,{"$f":"B10","v":100}," "]
["Coupon",2.5,{"$f":"B11","v":2.5}," "]
["NOM Debt",{"$f":"B11/C17","v":62.5},{"$f":"B12","v":62.5}," "]
["d",null,0.04," "]
["s",null,0.2," "]
["a",null,0.5," "]
["t",null,0," "]
["r(f)",null,0.04," "]
["OUTPUT"]
["VB   ",null,{"$f":"((((1-C16)*C11)/C17))*-C20/(1-C20)","v":31.249999999999996},"(1-C16)*(C11/C17)*(-C20/(-C20+1))"]
["b2",null,{"$f":"(0.5-(C17-C13)/(C14^2)-SQRT((0.5-(C17-C13)/(C14^2))^2+2*C17/(C14^2)))","v":-0.99999999999999978}," "]
["b2","(0.5-(C17-C13)/(C14^2)-SQRT((0.5-(C17-C13)/(C14^2))^2+2*C17/(C14^2)))"]